CODEWITHSUNDEEP
sqlpostgresql
candidus
candidus

Reputation: 129

SQL constraint depending on several tables

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?

Upvotes: 4

Views: 68

Answers (2)

Abelisto
Abelisto

Reputation: 15624

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

Upvotes: 2

Laurenz Albe
Laurenz Albe

Reputation: 248195

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.

Upvotes: 1

Related Questions