Enforcing UNIQUE KEY only on active devices mySQL

Question

How does one go about enforcing a UNIQUE KEY within a database that applies only in certain conditions? For a specific example the table consists of several columns which must remain unique only when another BIT column is set.

Simply adding the BIT column to the key is insufficient as there could be several retired devices of the same configuration.

This seems such a prevalent use case, surely there are several work-arounds if no inbuilt capability?

Answer 1

You are looking for a partial unique key. The solution in MySQL varies depending on your version.

In MySQL 8.0:

create table mydevices (
    id int primary key,
    device_id int,
    location_id,
    is_active tinyint(1),
    unique key (device_id, location_id, nullif(is_active, 0))
);

This takes advantage of the fact that null values are ignored when checking unique constraint. So the above allows duplicates on device_id / location_id when is_active is 0, while forbidding them when is_active is 1.