Unique constraints in couchdb

Question

Are there any techniques/proposals to enforce unique constraints? Yes, we can create key that's unique, but we cannot change key and keys and also this approach is not suitable for complicated validation (separate unique login, separate unique email, etc...)

For example, an Account should have unique login and email. Deriving a key from this fields will result in inconsistency:

key1: "[email protected]", { email: "[email protected]", login: "john"}
key2: "[email protected]", { email: "[email protected]", login: "mary"}

Looking good, but:

key1: "[email protected]", { email: "[email protected]", login: "mary"}
key2: "[email protected]", { email: "[email protected]", login: "mary"}

Oops, now we have 2 accounts with login: "mary"

Answer 1

It depends. Consider the multi-master replicated case, there could be conflicting entries added there consistent within each master, but not consistent once they replicate. You might only be using one couchdb server, but in general they design it assuming a multimaster case, and don't put in any features that would only work correctly in single unreplicated server.

If you only care about the single server case the conceivably you could rebuild your couchjs with networking support and perform an http query in your validate_doc_update() function that would perform a query against the DB to see if the email address already is already used and fail the update if so. Check here for more details about the validation mechanism.

I don't recommend doing that, instead I would embed all the uniqueness in the id field (either directly or via hashing) and just deal with moving the doc if the user changed anything that effected that.

Answer 2

This is one of the less fun bits of CouchDB. The best way I've found for handling unique fields that can change (like in your user example) is to create "pointer" documents with the unique values as a component of the key, and then use those to let you claim unique values. The important part of doing this is having a predictable key for the primary document, then saving the unique field claim documents prior to saving the primary (and letting key conflicts prevent the primary document from saving).

Given a user with a unique username and unique email, your primary documents might look like this:

user-1234: { username: "kurt", email: "kurt@localhost" }
user-9876: { username: "petunia", email: "[email protected]" }

The unique field pointers would look something like this:

user-username-kurt: { primary_doc: "user-1234" }
user-email-kurt@localhost: { primary_doc: "user-1234" }
user-username-petunia: { primary_doc: "user-9876" }
[email protected]: { primary_doc: "user-9876" }

Creating or updating a user would take these steps:

1. Prep your user document, generate a key for it if necessary
2. Save a "pointer" document for each changed unique field
3. If saving any of those fails, stop and fix errors
4. Save primary user document

Step 3 will take some thought. For instance, you won't want to try claim unique values for fields that haven't changed. You could, but then you'd have to put some additional logic in to handle a case where you're claiming a value for a user that already owns that value.

Step 3 would be a good place to let people take old claimed values as well. If one user has "released" the username kurt, for instance, I can just update that particular document to point to my new key after verifying that it's no longer in use. The alternative is to clear out claimed unique values when they change. I'm not sure which would be less work, really. Leaving stale claimed values in makes the most sense to me.

The interesting thing about this solution is that you don't need to use those pointer documents for anything once they're created. You can create views as normal on your user docs, and use them to query by email or username.

This setup also allows you to do relationships without having to worry about cascading user keys. I don't know about you, but my user documents are referenced by just about every other document in the system. Changing a user key would be a massive pain in the ass.