时间戳

首页 > 解决方案 > PostgreSQL 多个表的循环外键

sql - PostgreSQL 多个表的循环外键

问题描述

我有 3 张桌子。

聊天室

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

私人聊天室

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_roomand的外键约束private_chat_room。并且两者group_chat_room都有private_chat_roomFK 约束，指的是chat_room.

当我想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？

标签： sqlpostgresqlforeign-keys

解决方案

这里的主要问题是创建多个必需的双向外键。最终，您可能可以解决它。但它使数据模型更加复杂，代码也更加复杂。这是完全没有必要的。只需一张桌子即可完成您所拥有的一切。如果 group_chat_room 和 private_chat_room 是独立需要的，则为每个创建一个视图。此外，作为简单视图，它们是完全可更新的。
您可以通过将“pus_code”列移动到 chat_room 并添加 2 个布尔值来指示这是私人房间还是团体房间或两者兼而有之。是的，听起来很奇怪，你可以得到一个 private_group_chat_room。（注意：您的设计中没有任何东西可以阻止它，您得到的错误是因为它是必需的）。如果您确实想要，那么创建一个检查约束，要求至少 1 个布尔列为假。

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() 存在问题（不存在），因此对于演示，我更改为身份。在操作环境中会很好。

推荐阅读