sql - How to enforce unique constraint in MySQL?

Question

I have a MySQL table defined:

File
--------------------------
ID int(11) PK
name varchar(100)
customerId int(11) FK
isPrimaryImage tinyint(1)
....

I've read here that MySQL doesn't support a UNIQUE CONSTRAINT with a WHERE condition. For each customerId we'd have only one primaryImage = 1.

So what else can I do to enforce this constraint?

Answer 1

MySQL perfectly supports unique constraints.

It does not support partial constraints/indexes, though, so you would need to mark non-primary images with a NULL instead of 0.

ALTER TABLE file ADD CONSTRAINT ux_file_customer_primary 
UNIQUE (customerId, isPrimaryImage)

You can insert arbitrary number of NULL values into isPrimaryImage but only one non-null value per customer.