Search code examples
mysqlsqlmariadbconstraints

how to constrain a column value in MySQL based on other columns values?

The following table stores phone numbers with the name of the person who uses that phone number:

CREATE TABLE phone_numbers (
    phone_number VARCHAR(12) PRIMARY KEY,
    name TINYTEXT,
    default_number BOOLEAN
);

People can have multiple phone numbers but only one of the phone numbers belonging to a person can be their default phone number (deafault_number = 1).

How would you write a constraint to enforce that each person can only have 1 default_number=1?

Solution

  • You can use a unique index. In most databases, this would be a filtered unique index. In MySQL it instead uses expressions:

    create unique index unq_phone_numbers_name on
     phone_numbers(case when boolean then name end);

    A unique index allows multiple NULL values. But a given name can have only one number.

    That said, I would actually suggest storing the default number in the persons table, where the name is stored.