执行与完整性约束的“子集”关系的最佳方式是什么？ [英] What is the best way to enforce a 'subset' relationship with integrity constraints

问题描述

例如，给出了3个表：

• gastropod


• 蜗牛


• slug

，假设我们要执行

< gastropod中的每一行在snail或slug（但不是两个）中都有一个对应行

b在gastropod中，gastropod

中的每一行恰恰有一个相应的行在'gastropod'中有一个相应的行

设置我的模式以执行这些约束是什么最好的方法？

我为postgres提供了一个可能的答案，而我我对Postgres和Oracle的解决方案特别感兴趣，但也有兴趣了解其他RDBMS的解​​决方案。

EDIT

为了参考，从以下答案/评论中解决类似的问题：

• 数据库设计问题


• 数据库中的不同实体的相同数据 - 最佳实践 - 电话号码示例

解决方案

我自己的postgres解决方案（但我不知道是否是最好的方法）：

枚举：

 创建类型gastropod_type为枚举（'slug'，'蜗牛'）; 
  

表和约束：

 创建表gastropod（
 gastropod_id serial unique，
 gastropod_type gastropod_type，
 slug_gastropod_id integer，
 snail_gastropod_id integer，
 average_length numeric，
主键（gastropod_id，gastropod_type），
 check（（slug_gastropod_id为null，否则为0 else 1结束的情况）+ 
（当snail_gastropod_id为null时为0，否则为1））; 
 
创建表slug（
 gastropod_id integer unique，
 gastropod_type gastropod_type check（gastropod_type ='slug'），
 is_mantle_visible boolean，
主键（gastropod_id ，gastropod_type），
外键（gastropod_id，gastropod_type）
引用gastropod deferrable最初延迟）; 
 
创建表蜗牛（
 gastropod_id integer unique，
 gastropod_type gastropod_type check（gastropod_type ='snail'），
 average_shell_volume numeric，
主键（gastropod_id ，gastropod_type），
外键（gastropod_id，gastropod_type）
引用gastropod deferrable最初延迟）; 
 
 alter table gastropod 
添加外键（slug_gastropod_id，gastropod_type）
引用slug deferrable最初延迟; 
 
 alter table gastropod 
添加外键（snail_gastropod_id，gastropod_type）
引用蜗牛可延迟最初延迟; 
  

test：

 code> insert into gastropod（gastropod_type，slug_gastropod_id，average_length）
 values（'slug'，currval（'gastropod_gastropod_id_seq'），100）; 
 
插入到slug（gastropod_id，gastropod_type，is_mantle_visible）
值（currval（'gastropod_gastropod_id_seq'），'slug'，true）; 
 
选择gastropod_id，gastropod_type，average_length，is_mantle_visible 
从gastropod左外连接slug使用（gastropod_id，gastropod_type）
左外连接蜗牛使用（gastropod_id，gastropod_type）; 
 
 gastropod_id | gastropod_type | average_length | is_mantle_visible 
 -------------- + ---------------- + ------------- --- + ------------------- 
 1 | s lug | 100 | t 
（1行）
  

For example, given 3 tables:

• gastropod
• snail
• slug

and assuming we want to enforce that

1. every row in 'gastropod' has exactly one corresponding row in 'snail' or 'slug' (but not both)
2. every row in 'slug' has exactly one corresponding row in 'gastropod'
3. every row in 'snail' has exactly one corresponding row in 'gastropod'

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

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

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

• Maintaining subclass integrity in a relational database
• Database design problem
• Same data from different entities in Database - Best Practice - Phone numbers example

解决方案

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

enum:

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

tables and constraints:

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;

test:

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)

这篇关于执行与完整性约束的“子集”关系的最佳方式是什么？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！