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

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.

CodePudding user response：

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.