CODEWITHSUNDEEP
mysqlsql
Spektr
Spektr

Reputation: 875

Is it possible to have a MySQL constraint requiring a column to be unique *unless* it equals a specific value?

Let's say I have a table where I want the values of MY_COLUMN to be unique except when the value is 'xyz'. In other words, MY_COLUMN may be 'xyz' in multiple rows, but all values that are not 'xyz' must be unique. Is it possible to set a constraint that achieves this?

Upvotes: 6

Views: 521

Answers (3)

Steve
Steve

Reputation: 3703

Having thought about this I think I’ll provide my input.

As it has been correctly pointed out by other answers MySQL cannot have a UNIQUE index that has duplications.

I'm not going to debate the fact this couldn’t be prone to confusion nor if you ‘should’ even do something like this.

You could achieve what I believe to be the same results by having a regular index on my_column but also set it to NOT NULL (this is important as without this the trigger won’t throw an error on duplicate indexes). You can then create a 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

Upvotes: 0

Matt Westlake
Matt Westlake

Reputation: 3651

You can not set up a constraint to do this, however If you are doing this inside lets 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 and finally if they don't, run your insert statement. If you are doing this freelance maybe another column that is just a bool to say if true, then MY_COLUMN = 'xyz' else look at MY_COLUMN and get val. That's a tricky one.

Upvotes: 0

p.s.w.g
p.s.w.g

Reputation: 149030

According to the documentation

A UNIQUE index creates a constraint such that all values in the index must be distinct. An error occurs if you try to add a new row with a key value that matches an existing row. For all engines, a UNIQUE index permits multiple NULL values for columns that can contain NULL.

So create a nullable column and when you're querying your table just use ISNULL(MY_COLUMN, 'xyz').

Upvotes: 3

Related Questions