Hello, I do save usernames in a VARCHAR_IGNORECASE column. However the user can stay anonymous until a username is picked. On mysql a unique index is fine, as it allows multiple null-entries not violating the index. In the application layer I just map null to whatever the default username is. (i.e. Guest, etc.).
Now H2 seems to not support multiple nulls in a unique index, even in mysql compat mode. I don't consider this a bug, or mising feature, this seems to be undefined in the standard for some time now. And people already argue about null enough. ;) But I would like to emulate the behavior without autogenerating usernames for various reasons. But i would like to solve uniqueness on the database layer. I have looked into constraints, which seems to be what I am looking for, but currently struggle with the syntax: ALTER TABLE users ADD CONSTRAINT username_unique CHECK (??? LIKE 'guest' || FALSE=(SELECT username FROM users WHERE username like ??? LIMIT 1)) Now my plan-B is to just create another table with userID, username and unique Index and just join all the time, but I would like to avoid that, if possible/reasonable. Another alternative would be to use a foreign key here, and unique seems to allow exactly one null entry. Thanks for any input! -- -- You received this message because you are subscribed to the Google Groups "H2 Database" group. To unsubscribe from this group and stop receiving emails from it, send an email to [email protected]. To post to this group, send email to [email protected]. Visit this group at https://groups.google.com/group/h2-database. For more options, visit https://groups.google.com/d/optout.
