SQL constraint based on multiple tables

Question

Once I had a simple database:

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

But now the change in requirements: I must include the type (for example, a tool, a measuring device, etc.), and also ensure that we cannot have the same type of equipment for several workpieces.

So, I came up with the following:

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

Can this kind of redundancy be used to provide constraints? If so, should I discard the unique (id, equipment_type) in the working equipment of the table and instead enter the (id, equipment_type) primary key? Or is there an even better solution?

Answer 1

To do this, you need a functional unique index:

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

Test:

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

Answer 2

, .

, workequipment, id s.