Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

MySQL Unique Constraint based on column value

Let's say I have a table like this:

CREATE TABLE dept (
  id     VARCHAR(255) NOT NULL PRIMARY KEY,
  code   VARCHAR(255) NOT NULL,
  active BIT          NOT NULL,
  ...
);

Problem:

I want to add a unique constraint on code column. But it should be applied only if active is set to true (uniqueness should be checked only among active records). There can be many records with active = false and the same code so I can't use constraint on multiple columns.

What I tried:

I haven't found any references in the documentation proving that such constraint is possible, but I know it is possible in other databases using unique function-based indexes.

Of course I can write a trigger that will check the invariant on every add/update operation, but I hope there is more efficient solution.

I'm using MySQL 5.7.15.

like image 869
Sasha Shpota Avatar asked Oct 20 '25 12:10

Sasha Shpota


1 Answers

This simply isn't possible in MySQL, I'm afraid.

I have come "close" to solving this in the past by having a uniquely constrained column which is nullable (replacing both the active and code fields). When NULL - it's "inactive", when anything other than NULL - it has to be unique.

But that doesn't precisely solve the problem you're asking. (Perhaps something better can be suggested if you could update your question to include the bigger picture?)

Otherwise read/write to the table through a stored procedure or - as you've suggested yourself - do something inelegant with triggers.

like image 99
wally Avatar answered Oct 22 '25 04:10

wally



Donate For Us

If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!