如何为含联合主键的表创建SQL约束实现单设备唯一所有者

嘿，这个需求很常见嘛！要实现同一device_id下只能有一条is_owner为true的记录，但允许多条is_owner为false的记录，最优雅高效的方式是用部分/过滤唯一索引，不同数据库的具体写法我给你分情况说明：

PostgreSQL 方案 #

PostgreSQL原生支持部分唯一索引，直接创建即可：

CREATE UNIQUE INDEX idx_device_owner ON your_table_name (device_id)
WHERE is_owner = true;

这个索引只会对is_owner = true的记录生效，确保同一个device_id对应且仅对应一条这类记录，而is_owner = false的记录不受约束，可以随意添加多条。

MySQL 方案 #

MySQL 8.0.13及以上版本支持函数索引，我们可以利用这个特性实现：

CREATE UNIQUE INDEX idx_device_owner ON your_table_name (device_id, (is_owner = true));

如果你的MySQL版本较低，也可以通过生成列间接实现：

-- 先添加一个存储生成列
ALTER TABLE your_table_name ADD COLUMN owner_flag BOOLEAN GENERATED ALWAYS AS (is_owner = true) STORED;
-- 基于生成列创建唯一过滤索引
CREATE UNIQUE INDEX idx_device_owner ON your_table_name (device_id, owner_flag)
WHERE owner_flag = true;

SQL Server 方案 #

SQL Server支持过滤索引（Filtered Index），写法如下（假设is_owner是bit类型，用1表示true；如果是boolean类型直接写true即可）：

CREATE UNIQUE NONCLUSTERED INDEX idx_device_owner ON your_table_name (device_id)
WHERE is_owner = 1;

注意事项 #

• 优先选择索引方案，比触发器之类的实现性能更高，维护成本更低。
• 要确认你的数据库版本支持对应的特性：比如MySQL需要8.0.13+，SQL Server需要2008及以上。
• 记得把your_table_name替换成你实际的表名哦！

内容的提问来源于stack exchange，提问作者LEMUEL ADANE