How to implement unique constraints properly for simple or compound keys

As several questions ([1], [2], [3], …) already clarified, unique constraints are not a feature of couchbase but can be implemented using lookup documents. Here I would like to know how to choose the key of those lookup documents properly:

  • If I use a key pattern like user_unique::<email>, what conditions must hold about the email address?
  • Are there some special characters that are not allowed in document keys?
  • So I know that keys have a total length of 255 characters so an email can only have a max length of 255 - strlen("user_unique::") characters.

To overcome the length limit or possible character limitations, I thought of using the hashed email address in the key instead.

  • Which hashing algorithm could for instance be chosen? A consideration is N1QL compatibility. I did not find any hash function in the N1QL documentation so far, so querying those lookup documents from N1QL seems to be impossible. Did I miss it or could some kind of hash function become a future feature of N1QL that would supporting unique constraints? I expected something like SELECT * FROM bucket USE KEYS "user_unique::" || HASH(<some-email>)
    It should of course be really unlikely that the hash function produces collisions but on the other hand its output should also not be really long as all document keys are kept in memory.
  • Is it required to base64 encode the hash result or can a hash somehow be used in the key as raw byte array?

If I want a unique constraint on a combination of multiple fields (compound), how would this be properly implemented. I thought of a key naming like user_unique::<prename>::<lastname>::<email>.

  • One of the issues is that this reduces the possible length of the name and email 3x more!
  • Another issued I thought of are possible collisions. The Users (“Foo::”, “Bar”) and (“Foo”, “::Bar”) would both produce the equal lookup document key user_unique::Foo::::Bar::<email> and therefore collide. What is the recommended escape strategy for Couchbase’s recommended :: separator pattern and can it be applied easily in N1QL queries?

check UUID() works for you

I’m not sure how that answers my question. I’m storing user data in couchbase and each user does already have a UUID assigned as primary key/document id. This question is about additional unique constraints that I want to ensure when inserting data into the database. In my example each prename-lastname-email combination must be unique.

To solve this, the recommended way is (according to the posts I linked in my question) to use lookup documents. So whenever a new user is added to the database, an additional document gets inserted into the database whose key is comprised of the fields that should be unique. If there is already another user with the same values for those fields such a document exists already and we know that a user with the same prename-lastname-email combination exists already. This works since the key/value API is strongly consistent.

My question is about the best implementation of such a strategy. What would be the best naming strategy for these lookup documents.

As far as I know, N1QL doesn’t contain any hashing functions, so you would need to find a hashing algorithm in whatever language your application uses. As far as the allowed keys go, this is described here:

Basically, 250 UTF-8 characters with no spaces. (Though I’m told that not using spaces is a ‘best practice’ and that you probably can use spaces.)

1 Like

W.r.t. your question about key separators, in that situation you need to make sure that the user names are not permitted to contain whatever characters you are using as separators. So if you choose “::” to separate key sections, don’t allow user names to include “:”.