Hi,
Can you try a unique constraint instead?
See here
http://osdir.com/ml/h2-database/2012-03/msg00141.html
Ryan
On 3/05/2016 10:36 PM, sd23gl via H2 Database wrote:
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]
<mailto:[email protected]>.
To post to this group, send email to [email protected]
<mailto:[email protected]>.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
--
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.
