Search code examples
mysqlindexingwhere-clause

MySQL: Have unique values in a column with one exception

I've created a column of usernames but the ids for each row is linked to another table. If a user is deleted, I'd like to change the username to 'Deleted'. So I'd like to allow for unique values except for a deleted user.

I've come across this solution:

CREATE UNIQUE INDEX unique_user
ON User(Username)
WHERE Username != 'Deleted';

But MySQL throws an error saying "WHERE is not valid at this position, expecting EOF, ';'". Does MySQL not allow for the WHERE clause for a CREATE UNIQUE INDEX statement? Is there another way that having all unique values except one is possible?

Solution

  • You can create a UNIQUE index on Username when it's not Deleted and NULL otherwise, using the fact that NULL is not equal with itself to your advantage:

    CREATE UNIQUE INDEX unique_user
ON `User`((CASE WHEN  Username = 'Deleted' THEN null ELSE Username END));