SQL 约束取决于几个表

一旦我有了一个简单的数据库:

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)
);

可以使用这种冗余来强制执行约束吗？
如果是，我是否应该删除表 workequipment 中的 unique(id, equipment_type)
并将 (id, equipment_type) 设为主键？
或者有更好的解决方案吗？

最佳答案

您需要功能唯一索引:

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

关于SQL 约束取决于几个表，我们在Stack Overflow上找到一个类似的问题：https://stackoverflow.com/questions/48165005/