Search code examples
postgresqldatabase-designforeign-keysconstraintsreferential-integrity

Foreign key constraints involving multiple tables

I have the following scenario in a Postgres 9.3 database:

  • Tables B and C reference Table A.
  • Table C has an optional field that references table B.

I would like to ensure that for each row of table C that references table B, c.b.a = c.a. That is, if C has a reference to B, both rows should point at the same row in table A.

  • I could refactor table C so that if c.b is specified, c.a is null but that would make queries joining tables A and C awkward.
  • I might also be able to make table B's primary key include its reference to table A and then make table C's foreign key to table B include table C's reference to table A but I think this adjustment would be too awkward to justify the benefit.
  • I think this can be done with a trigger that runs before insert/update on table C and rejects operations that violate the specified constraint.

Is there a better way to enforce data integrity in this situation?

Solution

  • There is a very simple, bullet-proof solution. Works for Postgres 9.3 - when the original question was asked. Works for the current Postgres 13 - when the question in the bounty was added:

    Would like information on if this is possible to achieve without database triggers

    FOREIGN KEY constraints can span multiple columns. Just include the ID of table A in the FK constraint from table C to table B. This enforces that linked rows in B and C always point to the same row in A. Like:

    CREATE TABLE a (
  a_id int PRIMARY KEY
);

CREATE TABLE b (
  b_id int PRIMARY KEY
, a_id int NOT NULL REFERENCES a
, UNIQUE (a_id, b_id)  -- redundant, but required for FK
);

CREATE TABLE c (
  c_id int PRIMARY KEY
, a_id int NOT NULL REFERENCES a
, b_id int
, CONSTRAINT fk_simple_and_safe_solution
  FOREIGN KEY (a_id, b_id) REFERENCES b(a_id, b_id)  -- THIS !
);

    Minimal sample data:

    INSERT INTO a(a_id) VALUES
  (1)
, (2);

INSERT INTO b(b_id, a_id) VALUES
  (1, 1)
, (2, 2);

INSERT INTO c(c_id, a_id, b_id) VALUES
  (1, 1, NULL)  -- allowed
, (2, 2, 2);    -- allowed

    Disallowed as requested:

    INSERT INTO c(c_id, a_id, b_id) VALUES (3,2,1);

    ERROR:  insert or update on table "c" violates foreign key constraint "fk_simple_and_safe_solution"
DETAIL:  Key (a_id, b_id)=(2, 1) is not present in table "b".

    db<>fiddle here

    The default MATCH SIMPLE behavior of FK constraints works like this (quoting the manual):

    MATCH SIMPLE allows any of the foreign key columns to be null; if any of them are null, the row is not required to have a match in the referenced table.

    So NULL values in c(b_id) are still allowed (as requested: "optional field"). The FK constraint is "disabled" for this special case.

    We need the logically redundant UNIQUE constraint on b(a_id, b_id) to allow the FK reference to it. But by making it out to be on (a_id, b_id) instead of (b_id, a_id), it is also useful in its own right, providing a useful index on b(a_id) to support the other FK constraint, among other things. See:

    (An additional index on c(a_id) is typically useful accordingly.)

    Further reading: