Is it possible to have a MySQL constraint requiring a column to be unique * if it is not equal to a specific value?

Question

Let's say I have a table where I want MY_COLUMN values ​​to be unique, unless the value is "xyz". In other words, MY_COLUMN can be "xyz" in several lines, but all values ​​that are not "xyz" must be unique. Is it possible to set a limit that achieves this?

Answer 1

In accordance with the documentation

A UNIQUE index creates a constraint, so all values ​​in the index must be different. An error has occurred if you try to add a new row using the key value that matches the existing row. For all engines, the UNIQUE index allows multiple NULL values ​​for columns that may contain NULL.

So, create a column with a null value, and when you query your table, just use ISNULL(MY_COLUMN, 'xyz') .

Answer 2

You cannot set a restriction for this, however, if you do it inside, let's say a java project, you can do something like

 If (MY_COLUMN != "xyz"){ Get records of table with "xyz" in it X = //maping would equal "Select MY_COLUMN from table" (or select * from table depending on your mapping 

Then check to see if any match is found, and finally, if they don't, run the insert statement. If you are doing this freelancer, maybe another column, which is just a bool, to say if it is true, then MY_COLUMN = 'xyz' still look at MY_COLUMN and get val. This is a difficult question.

Answer 3

Thinking about this, I think I am giving my input.

As was correctly pointed out in other answers, MySQL cannot have a UNIQUE index that has duplication.

I am not going to discuss the fact that this cannot be confused, or if you should even do something like that.

You can achieve what, in my opinion, will be the same result by having a regular index on my_column, but also setting it to NOT NULL (this is important since without this the trigger will not generate an error for duplicate indices). Then you can create TRIGGER for my_table as follows:

 CREATE TRIGGER my_table_unique_index_with_exception BEFORE INSERT ON my_table FOR EACH ROW BEGIN IF NEW.my_column != 'xyz' THEN -- The value isn't 'xyz' so only one instance of this value is allowed -- check the count and if the value exists set it to NULL which will thorw -- an error as my_column annot be NULL IF (SELECT COUNT(*) FROM my_table WHERE my_table.my_row = NEW.my_column) > 0 THEN SET NEW.my_column = NULL; END IF; END