SQL constraint depending on several 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 requirements change: I have to include the type of workequipment (such as tool, measuring device etc.) as well and enforce that we can't have the same type of workequipment multiple times per workpiece.

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

Is it OK to use this kind of redundany to enforce constraints? If yes, should I drop the unique(id, equipment_type) in table workequipment and make (id, equipment_type) the primary key instead? Or is there an even better solution?

score 2 · Answer 1 · answered Jan 09 '18 at 11:30

2

You need functional unique index for that:

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

answered Jan 09 '18 at 11:30

Abelisto

14,826
2
33
41

I didn't know that you can create an index based on a function's return value, thanks for that. – candidus Jan 09 '18 at 11:42
@candidus You are welcome. Based on my experience when you have the task like check something on one table based on another one the functional constraint/index is the first thing that you have to think. – Abelisto Jan 09 '18 at 11:49
1

This solution is vulnerable against updating equipment_type without reindexing. I promise to never do that but I'm curious if there are ways to prevent such an update. Do you have any recommendations? – candidus Jan 10 '18 at 09:27
@candidus Good catch. If you asking about how to make a field readonly - there are a lot of such QA, for example: [Postgresql, Restrict update on specific columns (Read Only)](https://stackoverflow.com/q/15411735/593144) – Abelisto Jan 10 '18 at 10:26

score 1 · Answer 2 · answered Jan 09 '18 at 09:41

1

That is pretty ugly, but I cannot come up with a better solution either.

You would have to retain both the primary key and the unique constraint on workequipment, else you could end up with duplicate ids.

answered Jan 09 '18 at 09:41

Laurenz Albe

209,280
17
206
263

SQL constraint depending on several tables

2 Answers2