views:

456

answers:

2

I am new to CouchDB and learning about it. I did not come across CouchDB support for referential integrity. Can we create a foreign key for a field in the CouchDB document?

For e.g. Is it possible to ensure a vendor name used in a order document is available in the vendor database?

Does CouchDB support referential integrity? And Is it possible to make a field in a document as Primary key?

+5  A: 

No, CouchDB doesn't do foreign keys as such, so you can't have it handle the referential integrity of the system for you. You would need to handle checking for vendors in the application level.

As to whether you can make a field a primary key, the primary key is the _id field, but you can use any valid json as a key for the views on the db. So, for instance you could create a view of orders with their vendor as the key.

something like

function(doc) {
  if (doc.type == 'order')
    emit(doc.vendor,doc);
}

would grab all the docs in the database that have a type attribute with the value order and add them to a view using their vendor as the key.

Intro to CouchDB views

BaroqueBobcat
+4  A: 

These questions are incredibly relational database specific.

In CouchDB, or any other non-RDBMS, you wouldn't store your data the same way you would in an RDBMS so designing the relationship this way may not be best. But, just to give you an idea of how you could do this, lets assume you have a document for a vendor and a bunch of documents for orders that need to "relate" back to the vendor document.

There are no primary keys, documents have an _id which is a uuid. If you have a document for a vendor, and you're creating a new document for something like an order, you can reference the vendor documents _id.

{"type":"order","vendor-id":"asd7d7f6ds76f7d7s"}

To look up all orders for a particular vendor you would have a map view something like:

function(doc) { if (doc.type == 'order') {emit(doc['vendor-id'], doc)}}

The document _id will not change, so there is "integrity" there, even if you change other attributes on the vendor document like their name or billing information. If you stick the vendor name or other attributes from the vendor document directly in to the order document you would need to write a script if you ever wanted to change them in bulk.

Hope that helps a bit.

mikeal
Referential integrity is do-it-yourself, in other words. The programmer has to remember to create the vendor document first, then write the items that reference its **_id**, because CouchDB cannot enforce that for them. Similarly, the programmer upon deletion must remember themselves to first delete the orders associated with a vendor before removing the vendor — CouchDB won't do that for them. So CouchDB will let you constantly step into inconsistent states; there's no support for protection against it.
Brandon Craig Rhodes
I wouldn't call a dangling document with no reference to it an "inconsistent state" since the state in this case is the reference from the other document which would be removed. But your characterization of the semantics here is accurate. References are just indexes and removing a document does not infer some larger transaction that will clean up the back reference, although it would clean up the forward reference.
mikeal

related questions