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 h2-database+unsubscr...@googlegroups.com
<mailto:h2-database+unsubscr...@googlegroups.com>.
To post to this group, send email to h2-database@googlegroups.com
<mailto:h2-database@googlegroups.com>.
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 h2-database+unsubscr...@googlegroups.com.
To post to this group, send email to h2-database@googlegroups.com.
Visit this group at https://groups.google.com/group/h2-database.
For more options, visit https://groups.google.com/d/optout.
