Does CouchDB supports referential integrity?

Question

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?

Excruciate

Answer 1

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

Gca

Answer 2

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.

Trinitroglycerin

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. – Pikeman

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. – Trinitroglycerin

More importantly, a couchdb server is REST-3 capable (and you can build REST-4 capability if you want with design docs). Applying the term "referential integrity" makes no more sense for a document database than it does for a video game. It's not a relational database and doesn't pretend to be. – Bandicoot

Answer 3

While not possible to create an FK constraint, it is possible using Couch's Validate function

function(newDoc, oldDoc, userCtx, secObj) {
    if(newDoc && newDoc.type) switch(newDoc.type){
        case 'fish':
            var allSpecies = ['trout','goldfish'];
            if(!allSpecies.contains(newDoc.species)){
                throw({forbidden : 'fish must be of a know species'});
            }
            break;
        case 'mammals':
            if(!['M','F'].contains(newDoc.sex)){
                throw({forbidden : 'mammals must have their sex listed'});
            }
            break;
    }
}

Now, if a person were really clever (I'm not), they might do a call out to the DB itself for the list of Species... that would be a foreign key.

You may also want to read up on: How do I DRY up my CouchDB views?

Hobbema