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?