PostgreSQL cyclic foreign keys to multiple tables

Question

I have 3 tables.

chat_room

create table chat_room
(
    id uuid default uuid_generate_v4() not null
        constraint chat_room_pk
            primary key
        constraint fk__chat_room__group_chat_room
            references group_chat_room
                on update cascade on delete cascade
        constraint fk__chat_room__private_chat_room
            references private_chat_room
                on update cascade on delete cascade,
    name varchar(255) not null,
    description varchar(255),
    profile_pic varchar(128),
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone
);

group_chat_room

create table group_chat_room
(
    id uuid not null
        constraint group_chat_room_pk
            primary key
        constraint fk__group_chat_room___chat_room
            references chat_room
                on update cascade on delete cascade,
    pus_code char(7) not null
        constraint fk__group_chat_room__puskesmas
            references puskesmas
                on update cascade on delete cascade
);

create unique index group_chat_room_pus_code_uindex
    on group_chat_room (pus_code);

private_chat_room

create table private_chat_room
(
    id uuid not null
        constraint private_chat_room_pk
            primary key
        constraint fk__private_chat_room__chat_room
            references chat_room
                on update cascade on delete cascade
);

As you can see chat_room has foreign key constraints that refer to group_chat_room and private_chat_room. Also both group_chat_room and private_chat_room have FK constraints that refer to chat_room.

When I want to INSERT a row into group_chat_room I would use this

with chat_room as (
        insert into chat_room (id, name) values ('Some ID', 'Some Name')
    )
    insert into group_chat_room(id, pus_code) values ('Some ID', 'Some Code');

However because of those constraints this would produce an error

[23503] ERROR: insert or update on table "chat_room" violates foreign key constraint "fk__chat_room__private_chat_room" Detail: Key (id)=(cef8c655-d46a-4f63-bdc8-77113b1b74b4) is not present in table "private_chat_room".

How do I only insert to group_chat_room without having to insert it to private_chat_room?

Remove the FK constraint “depending on” the private chat root. There really should not be a bi-directional FK. If needing bi-directional FK for some strange reason, not evident here, see “deferred constraints”. These are not supported in all databases. — user2864740
– user2864740, Commented Aug 30, 2020 at 2:41
This code does not work in PostgreSQL as it is. You need to create the tables first, and add the foreign key constraint later. Also, make sure the foreign key constraints are marked as deferrable initially deferred in order to insert in these symmetric tables. — The Impaler
– The Impaler, Commented Aug 30, 2020 at 13:58

Belayer · Accepted Answer · 2020-08-30 22:56:52Z

The main problem here is creating multiple required bidirectional foreign keys. You can probably work around it, eventually. But it makes the data model more complicated and the code much more so. And it is completely unnecessary. Everything you have can be accomplished with just 1 table. If group_chat_room and private_chat_room are independently required then create a view for each. Further, as simple views, they are fully update-able.
You accomplish this by moving the column 'pus_code' to chat_room and adding 2 boolean values to indicate if this is a private or group room or both. Yes, strange as it sounds you can get a private_group_chat_room. (Note: there is nothing in your design preventing it and the error you are getting is because it is required). If you do want that then create a check constraint requiring at least 1 of the boolean columns be false.

create table chat_room
(
    id  integer generated always as identity
        constraint chat_room_pk
            primary key, 
    name varchar(255) not null,
    description varchar(255),
    profile_pic varchar(128),
    is_private boolean not null default false,
    is_group boolean not null default false,
    pus_code varchar(7) 
        constraint fk__group_chat_room__puskesmas
            references puskesmas
                on update cascade on delete cascade,        
    created_at timestamp with time zone default now() not null,
    updated_at timestamp with time zone,
    constraint not_group_of_pus_code_check
               check (   (not is_group and pus_code is null)
                      or (is_group and pus_code is not null)
                     ) 
);

-- create unique partial index
create unique index group_chat_room_pus_code_uindex on chat_room(pus_code)
    where is_group;

-- group_chat_room
create view group_chat_room 
       ( id   
       , name  
       , description 
       , profile_pic  
       , is_private  
       , pus_code 
       , created_at  
       , updated_at
       ) as 
  select id             
       , name           
       , description    
       , profile_pic    
       , is_private   
       , pus_code       
       , created_at     
       , updated_at 
   from chat_room
  where is_group;

-- private_chat_room
create view private_chat_room                    
       ( id                                     
       , name                                   
       , description                            
       , profile_pic                            
       , is_group                             
       , pus_code                               
       , created_at                             
       , updated_at                             
       ) as                                     
  select id                                     
       , name                                   
       , description                            
       , profile_pic                           
       , is_group                            
       , pus_code                                  
       , created_at                            
       , updated_at       
   from chat_room
  where is_private;

See fiddle for full example and a few tests. Note: fiddle has an issue with generate_uuid_v4() (did not exist) so for demo I changed to identity. It will fine in an operational environment.

Collectives™ on Stack Overflow

PostgreSQL cyclic foreign keys to multiple tables

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related