Keep strings case-insensitively unique in a database

I'd like to ensure that when a user wants to register a username in my system (web application), the user name is unique even if case is not regarded. So if a user named "SuperMan" is already registered, no other user 开发者_运维知识库must be allowed to register as "superman" or "SUPERman". This must be checked at database level.

In my current implementation, I do the following:

select count(*) from user where lower(name) = lower(?) for update;
-- If the count is greater than 0, abort with an error
-- Determine a new ID for the user
insert into user (id, name, …) values (?, ?, …);

I'm not sure whether the "for update" will lock the database far enough so that other users won't be able to register with an invalid name between the two SQL statements above. Probably it's no 100% safe solution. Unfortunately I cannot use unique keys in SQL because they will only compare case-sensitively.

Is there another solution to this? How about the following, to add more safety?

select count(*) from user where lower(name) = lower(?) for update;
-- If the count is greater than 0, abort with an error
-- Determine a new ID for the user
insert into user (id, name, …) values (?, ?, …);
-- Now count again
select count(*) from user where lower(name) = lower(?);
-- If the count is now greater than 1, do the following:
delete from user where id = ?;
-- Or alternatively roll back the transaction

The way I've done that is make a second column for the username that's either converted to all caps or all lower case. That I put a unique index on that. I use a trigger to generate the value in this other column so the code doesn't have to worry about it.

Come to think of it, you could use a function based index to get the same result:

CREATE UNIQUE INDEX user_name_ui1 ON user (lower(name))

Your assumption that an unique constraint will compare case sensitively is incorrect. They will compare according to the collation of the column. All commercial databases worth talking about support collations. Simply choose a case insensitive collation for your column, and declare a unique constraint on it. See:

• Character Sets and Collations That MySQL Supports
• Using SQL Server Collations
• Oracle Linguistic Sorting and String Searching
• SQLite collating sequences
• ...

Doing the enforcement by a lookup and before insert is not only extremely inefficient, is also incorrect under concurrency.

You can also change the way unique constraints work by changing the collation for that column: Unique constraint on table column