0

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?

1 Answer 1

3

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:

Without primary 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:

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.

With primary 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 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:

0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.