We currently have a single table which represents a bunch of equipment (of different types). Since the fields are pretty similar, they're stored in the same table.

select * from equipment;

id | name | type


1 | item 1 | CONVEYOR

2 | item 2 | PICKER

3 | item 3 | CONVEYOR

I want to create another table, which represents combinations of exactly 1 x PICKER and 1 x CONVEYOR. Something like:

CREATE TABLE "picker_conveyor_combo" (
  "id" serial,
  "picker_id" integer not null,
  "conveyor_id" integer not null

picker_id and conveyor_id would be foreign key references into the main equipment table.

However, I'd like to enforce that picker_id always references a row where the type is "PICKER" and similarly with conveyor.

I know that triggers are commonly used for this, but triggers don't typically validate historical data. So the fact the trigger exists won't guarantee that the data is valid.

I'm thinking of an alternative approach with composite foreign keys:

create table "picker_conveyor_combo" (
  id serial,
  "picker_id" integer not null,
  "picker_type" text not null,
  "conveyor_id" integer not null,
  "conveyor_type" integer not null

alter table "picker_conveyor_combo"
  add constraint "check_types"
  check ("picker_type" = 'PICKER' and "conveyor_type" = 'CONVEYOR');
alter table "picker_conveyor_combo"
  add constraint "picker_foreign"
  foreign key ("picker_id", "picker_type")
  references "equipment" ("id", "type");
alter table "picker_conveyor_combo"
  add constraint "conveyor_foreign"
  foreign key ("conveyor_id", "conveyor_type")
  references "equipment" ("id", "type");

In this method, there are some extra columns. However, all data will be validated based on constraints - so that adds some extra confidence.

Is this a valid approach, or is there something fundamentally wrong with the schema design?

1 Answer 1


Your approach is the one I would use.

You forgot a unique constraint:

ALTER TABLE equipment ADD UNIQUE(id, type);

That will be the target of the foreign key constraints.

I would not store type as a string. Rather, I would use a lookup table for the types and store the numeric identifier. This will prevent typos, and it will reduce the required storage space.

  • Thanks, yup needed the unique constraint so the foreign key could be added.
    – J3Y
    Commented Oct 10, 2019 at 7:23

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.