强制执行具有完整性约束的“子集"关系的最佳方法是什么

2021-12-26 00:00:00 postgresql mysql oracle sql-server database-design

例如，给定 3 个表:

For example, given 3 tables:

  • 腹足类
  • 蜗牛
  • 蛞蝓

并假设我们要强制执行

  1. 'gastropod' 中的每一行在 'snail' 或 'slug' 中都有一个对应的行(但不能同时存在)
  2. 'slug' 中的每一行在 'gastropod' 中都有一个对应的行
  3. 'snail' 中的每一行在'gastropod' 中都有一个对应的行

设置架构以强制执行这些约束的最佳方法是什么?

what is the best way to set up my schema to enforce these constraints?

我已经为 postgres 提供了一个可能的答案，我对 postgres 和 Oracle 的解决方案特别感兴趣，但也有兴趣查看其他 RDBMS 的解决方案

I've provide one possible answer for postgres, and I am particularly interested in solutions for postgres and Oracle, but would also be interested to see solutions for other RDBMSs

编辑
作为参考，来自以下回答/评论的 SO 问题解决了类似的问题:

EDIT
For reference, SO questions from from answers/comments below addressing similar problems:

  • 维护关系数据库中的子类完整性
  • 数据库设计 - 文章、博文、照片、故事
  • 来自不同实体的相同数据在数据库中 - 最佳实践 - 电话号码示例

推荐答案

我自己的 postgres 解决方案(但我不知道它是否是最好的方法):

My own solution for postgres (but I have no idea if it is the best way):

枚举:

create type gastropod_type as enum ('slug', 'snail');

表和约束:

create table gastropod(
  gastropod_id serial unique,
  gastropod_type gastropod_type,
  slug_gastropod_id integer,
  snail_gastropod_id integer,
  average_length numeric,
  primary key(gastropod_id, gastropod_type),
  check( (case when slug_gastropod_id is null then 0 else 1 end)+
         (case when snail_gastropod_id is null then 0 else 1 end)=1) );

create table slug(
  gastropod_id integer unique,
  gastropod_type gastropod_type check (gastropod_type='slug'),
  is_mantle_visible boolean,
  primary key(gastropod_id, gastropod_type),
  foreign key(gastropod_id, gastropod_type) 
    references gastropod deferrable initially deferred );

create table snail(
  gastropod_id integer unique,
  gastropod_type gastropod_type check (gastropod_type='snail'),
  average_shell_volume numeric,
  primary key(gastropod_id, gastropod_type),
  foreign key(gastropod_id, gastropod_type)
    references gastropod deferrable initially deferred );

alter table gastropod 
add foreign key(slug_gastropod_id, gastropod_type) 
references slug deferrable initially deferred;

alter table gastropod 
add foreign key(snail_gastropod_id, gastropod_type) 
references snail deferrable initially deferred;

测试:

insert into gastropod(gastropod_type, slug_gastropod_id, average_length)
values ('slug', currval('gastropod_gastropod_id_seq'), 100);

insert into slug(gastropod_id, gastropod_type, is_mantle_visible)
values (currval('gastropod_gastropod_id_seq'), 'slug', true);

select gastropod_id, gastropod_type, average_length, is_mantle_visible
from gastropod left outer join slug using(gastropod_id, gastropod_type) 
               left outer join snail using(gastropod_id, gastropod_type);

 gastropod_id | gastropod_type | average_length | is_mantle_visible
--------------+----------------+----------------+-------------------
            1 | slug           |            100 | t                 
(1 row)

相关文章