-1

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?

user4157124
  • 2,809
  • 13
  • 27
  • 42
Artem
  • 358
  • 3
  • 15

1 Answers1

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)
                                            );
Belayer
  • 13,578
  • 2
  • 11
  • 22
  • Now I have something like this table structure and the main drawback is that when adding a new type, I will have to add a new column. – Artem Aug 14 '23 at 07:55
  • Yes you will. Another approach would be combine them into a single type table. If the attributes are the same or close then this is a viable option. If there is an access issue then look into [Row Level Security](https://www.postgresql.org/docs/current/ddl-rowsecurity.html). Without knowing details one cannot suggest which is better. – Belayer Aug 14 '23 at 16:31