Question 255799 in Mysql Question posted in
The official MySQL documentation can be found here.

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?

Tags:

2

Answers


  1. 0

    The task is addressing the scenario where you must keep data, but it needs to be anonymized. E.g. you need to know that some user ordered product xy in January 2017, but laws force you to become oblivious to which user that was.

    MySQL does not support conditional indexes (indexes with a WHERE clause). But you can create a function index including the ID when a user is deleted:

    CREATE UNIQUE INDEX unique_user
  ON User(Username, (CASE WHEN  Username = 'Deleted' THEN id ELSE 0 END));

    Demo: https://dbfiddle.uk/HDWnVeKl

    Login or Signup to reply.
  2. 0

    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));
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search