Can I conditionally enforce a uniqueness constraint?

mysql unique-constraint
user2692862 picture user2692862 · Aug 17, 2013 · Viewed 7.5k times · Source

My database contains a table of users. Every active user has a unique username. I'd like to be able to deactivate a user and free up the username they're using, but keep them in the same table.

Is there a way to only conditionally enforce the uniqueness constraint?

Answer

Gordon Linoff picture Gordon Linoff · Aug 18, 2013

Add another column called something like isactive. The create a unique constraint on (username, isactive).

Then you can have both an active and inactive user name at the same time. You will not be able to have two active user names.

If you want multiple inactive names, use NULL for the value of isactive. NULL values can be repeated in a unique index.

Related questions

How do I specify unique constraint for multiple columns in MySQL?

I have a table: table votes ( id, user, email, address, primary key(id), ); Now I want to make the columns user, email, address unique (together). How do I do this in MySql? Of course the example is just... an example. …

Read More
MySQL - Make an existing Field Unique

I have an already existing table with a field that should be unique but is not. I only know this because an entry was made into the table that had the same value as another, already existing, entry and this …

Read More
MySQL delete multiple rows in one query conditions unique to each row

So I know in MySQL it's possible to insert multiple rows in one query like so: INSERT INTO table (col1,col2) VALUES (1,2),(3,4),(5,6) I would like to delete multiple rows in a similar way. I know it's possible to delete multiple …

Read More