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 FOREIGN KEY constraint can only ever reference a single target table by design.
Maybe partitioning or inheritance fits your use case?
If not, here are possible workarounds:
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:
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 here
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.
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 here
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: