Postgres 13

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

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

CREATE TABLE



CREATE TABLE



CREATE TABLE


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

INSERT 0 2



INSERT 0 2



INSERT 0 2


-- disallowed
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".