CODEWITHSUNDEEP
mysqlunique-constraint
Angelino Mehmeti
Angelino Mehmeti

Reputation: 109

How do I set a column to be unique, but only under a condition?

I have a table, which has an ID, a column "A" and other columns. If A is set to "this", there can't be another ID with it's column A set to "this". Else, there can be many entries of that ID with multiple values set for column "A".

Ex.:

ID | A | other columns

this is allowed:

1 | that | ...
1 | something | ...
1 | foo | ...

but this is not:

1 | this | ...
1 | that | ...

(the 3 dots mean it doesn't matter what data we have there)

I'm doing this on MySQL Workbench, so it would be much appreciated if your answer showed me how to do it there.

Upvotes: 1

Views: 42

Answers (1)

Kamil Gosciminski
Kamil Gosciminski

Reputation: 17177

You need to create a trigger that will fire before every INSERT or UPDATE statement (for each row) and check whether or not your constraint is valid and the row can or cannot be added. AFAIK in MySQL you actually need to have two triggers (one for each action), but there is nothing stopping you from wrapping up your validation within a procedure and call it from both triggers.

I'll give you the starters for further tweaking.

1.Create insert trigger

DELIMITER //
DROP TRIGGER IF EXISTS insert_trg //
CREATE TRIGGER insert_trg 
  BEFORE UPDATE ON yourtable
  FOR EACH ROW
BEGIN
  CALL yourprocedure(<arguments here>);
END //
DELIMITER ;

2.Create update trigger analogously to the insert trigger

3.Create procedure with the validation code

SQL to validate your constraint will look something like:

SELECT *
FROM yourtable yt
WHERE yt.id = NEW.id -- replace with arguments passed to procedure
  AND yt.A  = NEW.A  -- same as above

You most likely need to wrap it up with an EXISTS statement and if statement

Some source of knowledge:

Upvotes: 1

Related Questions