Constraint that checks there is only one is_primary true in one to many group

user_2612651 0
Viewed 36

In a one-to-many table setup say customer to phone. Where one customer can have many phones.

The table for phone has a column is_primary so that the applications knows which one to use under normal circumstances. There can only be one is_primay that is true and n that can be false.

Is there any constraint in MySQL that can be applied to enforce the above scenario? The application currently protects against this but we wanted to add a block at the db.

A unique constraint does not allow for the n number of false phone. It would make is so only 1 true and 1 false per customer.

There is the idea of a partial unique constraint but I cannot find docs in MySQL for this just postgres.

mysql sql
1 Answers

UNIQUE constraints in MySQL (as per the standard) ignore NULLs. So you could define the column as the following:

create table mytable (
  is_primary tinyint null,
  check(is_primary = 1),
  unique key (is_primary)
  );
  
insert into mytable values (1); -- ok

insert into mytable values (null); -- ok
insert into mytable values (null); -- ok
insert into mytable values (null); -- ok

insert into mytable values (1); -- duplicate

insert into mytable values (2); -- error

MySQL supports CHECK constraints starting at version 8.0.16. If you use an earlier version of MySQL, you would need to enforce this with a trigger, or else just make sure you client code never tries to insert a value other than 1 or null.

Here's another solution inspired by Akina's comment:

create table mytable (
  is_primary tinyint null,
  check(is_primary in (0,1)),
  unique key ((nullif(is_primary, 0)))
);

This uses MySQL 8.0 expression indexes, so you can still insert 0 or 1 into the is_primary column, but 0's will be indexed as if they are NULLs, which means the UNIQUE constraint ignores it.

I can't create a dbfiddle for this, because dbfiddle is still using MySQL 8.0.12, and that's too old to support expression indexes. I tested this on 8.0.28 and it works.

Bill_Karwin_208600
Related