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.
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.
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With