mysql constraint unique when boolean field is true

Question

I have this table:

create table expert_country (
    expert_id  varchar(36) not null,
    country_id varchar(36) not null,
    main       boolean     not null default false,
    primary key (expert_id, country_id),
    constraint foreign key (expert_id) references expert (id),
    constraint foreign key (country_id) references country (id),
    -- constraint i'm looking for
);

But I can't figure out the constraint I need to add to have only one main expert per country.

I tried constraint unique (country_id, true) and constraint unique (*, country_id, true) but it is not valid sql. Any ideas?

Answer 1

You can make use of unique keys ignoring null (which is following the sql standard): any tuple containing null is not equivalent to any tuple. That means, that in a unique index (country_id, main), you can have the row (1, null) as often as you want, but at most one row (1, 1) and one row (1, 0).

You would need to allow null for main, and would need to use 1 and null (not 0) to encode if it is an expert or not (otherwise you can only have one non-expert). The constraint would then be:

main boolean null default null,
constraint unique index uidx_expert_country (country_id, main)

This might require changes to your application, might look sketchy (as false is obviously not the same as null) and might soften up the interpretation of your column (as it now can contain 3 values null, 0/false and 1/true although you can prevent that with an additional foreign key to a table with a single row containing 1).

So if you do not want to do that, you can define an additional column that will be calculated in a trigger or as a generated column, and then define a unique index on that "dummy"-column:

main boolean  not null default false,
main_unq  boolean as (if(main = true,true, null)) stored,
constraint unique index uidx_expert_country (country_id, main_unq)

For a different way to encode the information which expert is the main expert of a country, you could e.g. add a column main_expert_id to your country-table (and a foreign key to expert_country to verify that the combination exists) and remove the column main.