如何在 MySQL 中强制执行唯一约束?
我定义了一个 MySQL 表:
I have a MySQL table defined:
File
--------------------------
ID int(11) PK
name varchar(100)
customerId int(11) FK
isPrimaryImage tinyint(1)
....
我在这里读到 MySQL 不支持带有 WHERE 条件的 UNIQUE CONSTRAINT.对于每个 customerId，我们只有一个 primaryImage = 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?
推荐答案
MySQL 完美支持唯一约束.
不过，它不支持部分约束/索引，因此您需要使用 NULL 而不是 0 来标记非主要图像.
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)
您可以在 isPrimaryImage 中插入任意数量的 NULL 值，但每个客户只能插入一个非空值.
You can insert arbitrary number of NULL values into isPrimaryImage but only one non-null value per customer.
相关文章