Enforcing constraints "two tables away"

I ran into some trouble modeling an electrical schematic in SQL.
The structure I'd like to capture is

part ←────────── pin
   ↑                ↑
part_inst ←───── pin_inst

where "inst" is short for "instance".

For example, I might have as a part an LM358 op-amp with pins
1OUT, 1IN-, 1IN+, GND, 2IN+, 2IN-, 2OUT, and VCC. I might
then place this part on a schematic, creating a part_inst and 8
pin_insts.

Ignoring data fields, my initial attempt at a schema was

create table parts (
    part_id bigserial primary key
);
create table pins (
    pin_id bigserial primary key,
    part_id bigint not null references parts
);
create table part_insts (
    part_inst_id bigserial primary key,
    part_id bigint not null references parts
);
create table pin_insts (
    pin_inst_id bigserial primary key,
    part_inst_id bigint not null references part_insts,
    pin_id bigint not null references pins
);

The main problem with this schema is that a pin_inst might be tied
to a part_inst with part_id=1 but its pin has part_id=2.

I'd like to avoid this problem on the database level rather than the
application level. So, I modified my primary keys to enforce that.
I marked the changed lines with --.

```
create table parts (
part_id bigserial primary key
);
create table pins (
pin_id bigserial, --
part_id bigint not null references parts,
primary key (pin_id, part_id) --
);
create table part_insts (
part_inst_id bigserial, --
part_id bigint not null references parts,
primary key (part_inst_id, part_id) --
);
create table pin_insts (
pin_inst_id bigserial primary key,
part_inst_id bigint not null, --
pin_id bigint not null, --
part_id bigint not null references parts, --
foreign key (part_inst_

Minimal solution

A radical solution would be to remove pin_inst completely:
part ←────────── pin
↑ ↑
part_inst ←───── pin_inst

There is nothing in your question to suggest you actually need the redundant table. For pins associated to a part_inst, look at the pins of the associated part.

That would simplify the code to:

CREATE TABLE part (   -- with singular terms as table names
  part_id      bigserial PRIMARY KEY
);
CREATE TABLE pin (
  pin_id       bigserial PRIMARY KEY,
, part_id      bigint NOT NULL REFERENCES part
);
CREATE TABLE part_inst (
  part_inst_id bigserial PRIMARY KEY,
, part_id      bigint NOT NULL REFERENCES part
);

But your comment made clear that we won't get away with that ...
Alternative if pin_inst is needed

You cannot reference a table “two tables away” with foreign key constraints. Including part_id like you did is the simplest solution with foreign key constraints.

But you can at least make do without "polluting" the primary keys. Add UNIQUE constraints.

(Updated with modern syntax for Postgres 14.)
CREATE TABLE part (
part_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
);

CREATE TABLE pin (
pin_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, part_id bigint NOT NULL
, CONSTRAINT pin_part_id_fkey FOREIGN KEY (part_id) REFERENCES part
, CONSTRAINT pin_fk_uni UNIQUE (part_id, pin_id) -- only for FK; note leading part_id
);

CREATE TABLE part_inst (
part_inst_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, part_id bigint NOT NULL
, CONSTRAINT part_inst_part_id_fkey FOREIGN KEY (part_id) REFERENCES part
, CONSTRAINT part_inst_fk_uni UNIQUE (part_id, part_inst_id) -- only for FK
);

CREATE TABLE pin_inst (
pin_inst_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY
, part_inst_id bigint NOT NULL
, pin_id bigint NOT NULL
, part_id bigint NOT NULL
, CONSTRAINT pin_fkey FOREIGN KEY (part_id, pin_id) REFERENCES pin (part_id, pin_id)
, CONSTRAINT part_inst_fkey FOREIGN KEY (part_id, part_inst_id) REFERENCES part_inst (part_id, part_inst_id)
);

db<>fiddle here

The bold UNIQUE constraints are logically redundant, but required as target for the bold FOREIGN KEY constraints.

I put part_id first in the unique constraints (and consequently in the FK constraints, too). That is irrelevant for referential integrity, but it matters for performance. The primary keys already implement indexes for the PK columns. It's better to have the other column first in the multicolumn indexes implementing the UNIQUE constraints. See:

• Working of indexes in PostgreSQL

• Is a composite index also good for queries on the first field?

• Many to Many and Weak Entities

Related questions on SO:

• Can a multi-field UNIQUE constraint include fields from more than one table?

• Foreign key constraint with some column values residing in other tables

Alternative with triggers

You could resort to triggers functions, which are more flexible, but a bit more complicated and error prone and a bit less strict. The benefit: you could do without part_inst.part_id and pin.part_id ...

StackExchange Database Administrators Q#58970, answer score: 15