依赖于多个表的SQL约束
依赖于多个表的SQL约束
提问于 2018-01-09 17:28:11
曾经我有一个简单的数据库：
CREATE TABLE workpiece
(
id serial primary key
-- More columns, not important here
);
CREATE TABLE workequipment
(
id serial primary key
-- More columns, not important here
);
CREATE TABLE workpiece_workequipment
(
workpiece_id integer not null references workpiece(id),
workequipment_id integer not null references workequipment(id),
primary key(workpiece_id, workequipment_id)
);但现在需求发生了变化:我必须包括工作设备的类型(如工具、测量设备等)。同样，我们不能在每个工件上多次使用相同类型的工作设备。
因此，我想出了以下几点：
CREATE TABLE workpiece
(
id serial primary key
-- More columns, not important here
);
CREATE TABLE workequipment
(
id serial primary key,
equipment_type integer, -- An enum in real world
-- More columns, not important here
unique(id, equipment_type)
);
CREATE TABLE workpiece_workequipment
(
workpiece_id integer not null references workpiece(id),
workequipment_id integer not null,
workequipment_type integer not null,
primary key(workpiece_id, workequipment_id),
foreign key(workequipment_id, workequipment_type) references workequipment(id, equipment_type),
unique(workpiece_id, workequipment_type)
);是否可以使用这种冗余来强制执行约束？如果是，我是否应该删除表工作设备中唯一的(id，equipment_type)，而将(id，equipment_type)作为主键？或者还有更好的解决方案？
Stack Overflow用户
发布于 2018-01-09 19:30:58
为此，您需要使用函数唯一索引：
CREATE TABLE workpiece
(
id serial primary key
-- More columns, not important here
);
CREATE TABLE workequipment
(
id serial primary key,
equipment_type integer
-- More columns, not important here
);
CREATE TABLE workpiece_workequipment
(
workpiece_id integer not null references workpiece(id),
workequipment_id integer not null references workequipment(id),
primary key(workpiece_id, workequipment_id)
);
-- Magic starts here :)
create function get_workequipment_type(int) returns int immutable language sql as $$
select equipment_type from workequipment where id = $1
$$;
create unique index idx_check_wetype_unique
on workpiece_workequipment(workpiece_id, get_workequipment_type(workequipment_id));测试：
insert into workpiece values(default);
insert into workequipment(equipment_type) values(1),(1),(2);
insert into workpiece_workequipment values(1,1),(1,3); -- Works
--insert into workpiece_workequipment values(1,1),(1,2); -- Fails页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/48165005
复制相关文章
相似问题
腾讯云开发者
Copyright © 2013 - 2026 Tencent Cloud. All Rights Reserved. 腾讯云 版权所有
深圳市腾讯计算机系统有限公司 ICP备案/许可证号：粤B2-20090059 
粤公网安备44030502008569号
腾讯云计算（北京）有限责任公司 京ICP证150476号 | 京ICP备11018762号