Creating a composite foreign key for different tables using id and ENUM

Question

I'm designing a PostgreSQL database schema where I need to establish a composite foreign key that references various tables based on an ENUM value stored in another column. In main_table has columns target_id and target_type. target_type column holds values from an ENUM type ('TYPE_A', 'TYPE_B' and 'TYPE_C').

My objective is to create a composite foreign key that links to distinct tables (table_a, table_b and table_c) based on target_type. For instance, when target_type is 'TYPE_A' I aim for target_id to function as a foreign key pointing to id column in table_a. Something like this (but this example doesn't work):

ALTER TABLE main_table
    ADD CONSTRAINT fk_main_table_target_a 
        FOREIGN KEY (target_id, target_type)
            REFERENCES table_a (id, 'TYPE_A');

How to implement this dynamic foreign key setup using PostgreSQL? Could someone provide me with a detailed example of creating such a composite foreign key, ideally with an ENUM value-based selection?

Answer 1

How to implement this dynamic foreign key setup using PostgreSQL. If by dynamic foreign key you mean defining a single column that can become an FK to different tables well that cannot be done. A FK column must always refer to the same table. You resolve this by creating a column for each table then validating only one of them is populated. Further this supersedes the need for a composite foreign key.

create type t_enum as enum('TYPE_A','TYPE_B','TYPE_C');
 
create table main_table 
           ( main_id       integer  generated always as identity
                                    primary key 
           , target_type   t_enum
           , type_a_id     integer  references table_a(id) 
           , type_b_id     integer  references table_b(id)
           , type_c_id     integer  references table_c(id) 
           -- other attributes ...
           , constraint just_one_abc CHECK (num_nonnulls(type_a_id,type_b_id,type_b_id) = 1)
           );

If you need to validate type vs. the referenced table then add a second constraint:

alter table main_table 
         add constraint match_one_abc CHECK (    (target_type = 'TYPE_A'::t_enum and type_a_id is not null)
                                              or (target_type = 'TYPE_B'::t_enum and type_b_id is not null)
                                              or (target_type = 'TYPE_C'::t_enum and type_C_id is not null)
                                            );