多个表的PostgreSQL循环外键

Question

我有三张桌子。

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
);

如您所见，chat_room具有引用group_chat_room和private_chat_room的外键约束。此外，group_chat_room和private_chat_room都有引用chat_room的FK约束。

当我想将一行INSERT转换为group_chat_room时，我会使用以下方法

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');

然而，由于这些限制，这将产生一个错误。

[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".

如何只插入到group_chat_room而不必将其插入到private_chat_room

Answer 1

这里的主要问题是创建多个所需的双向外键。也许你最终能解决这个问题。但是它使得数据模型更加复杂，代码更加复杂。它是完全不必要的。你拥有的一切都可以用一张桌子就能完成。如果需要独立的group_chat_room和private_chat_room，那么为每个视图创建一个视图。此外，作为简单的视图，它们是完全可更新的。

您可以通过将列'pus_code‘移动到chat_room并添加2个布尔值来指示这是一个私有或组间，还是两者兼而有之。是的，听起来很奇怪，你可以得到一个private_group_chat_room。(注意:在您的设计中没有任何东西可以阻止它，而您所得到的错误是因为它是必需的)。如果您确实希望这样做，那么创建一个check约束，要求至少有一个布尔列为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;

有关完整的示例和一些测试，请参见小提琴。注意: generate_uuid_v4() (不存在)有问题，所以为了演示，我更改为标识。在操作环境中它会很好。