Foreign key refencing multiple primary keys



  • I have multiple tables sharing a common unique ID in my Postgres database.

    Parent tables:

    table1 - id1
    table2 - id2
    table3 - id3
    

    id1, id2, and id3 are unique across the database.

    Child table:

    table_child - id  -- referencing id1, id2, id3
    

    id can have any value from id1 or id2 or id3.

    Is this kind of design possible?



  • A https://www.postgresql.org/docs/current/ddl-constraints.html#DDL-CONSTRAINTS-FK can only ever reference a single target table by design.

    Maybe https://www.postgresql.org/docs/current/ddl-partitioning.html or https://www.postgresql.org/docs/current/ddl-inherit.html fits your use case?
    If not, here are possible workarounds:

    Without master table

    You need to enforce uniqueness of id values across table1 - table3 some other way. This solution does not take care of it.

    We can work with the behavior of default match type MATCH SIMPLE of FOREIGN KEY constraints: If at least one column of an outgoing multicolumn FK constraint is NULL, the constraint is not enforced for the row. See:

    • https://dba.stackexchange.com/questions/58894/differences-between-match-full-match-simple-and-match-partial/71508#71508

    This way we can switch FK constraints on and off on demand:

    CREATE TABLE table1 (
      id1  integer
    , t1   bool NOT NULL DEFAULT true CHECK (t1)
    , col1 text
    , CONSTRAINT t1_uni_for_mixed_fk UNIQUE (id1, t1)
    );
    

    CREATE TABLE table2 (
    id2 integer PRIMARY KEY
    , t2 bool NOT NULL DEFAULT true CHECK (t2)
    , col2 text
    , CONSTRAINT t2_uni_for_mixed_fk UNIQUE (id2, t2)
    );

    CREATE TABLE table3 (
    id3 integer PRIMARY KEY
    , t3 bool NOT NULL DEFAULT true CHECK (t3)
    , col3 text
    , CONSTRAINT t3_uni_for_mixed_fk UNIQUE (id3, t3)
    );

    CREATE TABLE child (
    child_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    , mix_id integer NOT NULL
    , t1 bool
    , t2 bool
    , t3 bool
    , CONSTRAINT max_one_source CHECK (num_nonnulls(t1, t2, t3) < 2)
    -- , CONSTRAINT exactly_one_source CHECK num_nonnulls(t1, t2, t3) = 1 -- or this?
    , CONSTRAINT child_mix_id1_fk FOREIGN KEY (mix_id, t1) REFERENCES table1 (id1, t1)
    , CONSTRAINT child_mix_id2_fk FOREIGN KEY (mix_id, t2) REFERENCES table2 (id2, t2)
    , CONSTRAINT child_mix_id3_fk FOREIGN KEY (mix_id, t3) REFERENCES table3 (id3, t3)
    );

    db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=fc7dcb8eeb273bf2e6d3fdda6f5a11e6

    The CHECK constraint max_one_source enforces at most one FK.
    The alternative CHECK constraint exactly_one_source enforces exactly one FK. The columns tabl1.t1 etc. are logically redundant but required for the multicolumn FK constraint. (Unfortunately, we cannot just use a constant in the FK definition.) I made each default to true and accept no other value.

    The UNIQUE constraints t1_uni_for_mixed_fk etc. include the logically redundant t1 etc. Required for the FK constraint. (Can replace a simple PK on just id1 etc.

    With master table

    To enforce your model, create a central table holding a central PRIMARY KEY. This way, uniqueness of id across table1 - table3 is also enforced reliably.

    CREATE TABLE central (
      central_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    , kind "char" CHECK (kind IN ('1', '2', '3'))
    , CONSTRAINT unique_for_fk UNIQUE (central_id, kind)
    );
    

    CREATE TABLE table1 (
    id1 integer PRIMARY KEY
    , kind "char" NOT NULL DEFAULT '1' CHECK (kind = '1')
    , col1 text
    -- more ...
    , CONSTRAINT table1_id1_central_fk FOREIGN KEY (id1, kind) REFERENCES central (central_id, kind)
    );

    -- CREATE TABLE table2 ... ;
    -- CREATE TABLE table3 ... ;

    CREATE TABLE child (
    child_id integer GENERATED ALWAYS AS IDENTITY PRIMARY KEY
    , central_id integer NOT NULL REFERENCES central
    -- more ...
    );

    Inserts into table_a etc. could look like this:

    -- generate ID of kind "a" in a CTE
    WITH cte AS (INSERT INTO central(kind) VALUES ('1') RETURNING central_id)
    INSERT INTO table1 (id1, col1)
    SELECT central_id, 'col1_value'
    FROM   cte;
    

    db<>fiddle https://dbfiddle.uk/?rdbms=postgres_14&fiddle=6e0d496a61950044215846263a0b7c7d

    Each ID can only be of one kind, and any "child" table has single point of reference.

    Column kind is logically redundant in table_a etc., but required for the FK constraint. I made it so that nothing but the right value can be entered.

    The UNIQUE constraint unique_for_fk is also logically redundant. Required for the FK constraint.

    Related:

    • https://dba.stackexchange.com/questions/58970/enforcing-constraints-two-tables-away/58972#58972



Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2