强制执行“子集”的最佳方法是什么？与完整性约束的关系

Question

例如，给定 3 个表：

• gastropod
• snail
• slug

，并假设我们要强制

1. “gastropod”中的每一行在“snail”或“slug”（但不是两者）中恰好有一个对应行，
2. “slug”中的每一行都恰好有一个对应行“gastropod”中的对应行
3. “snail”中的每一行都与“gastropod”中的一个对应行

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

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

编辑
作为参考，下面的答案/评论中的问题解决了类似的问题：

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

Answer 1

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

枚举：

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)

Answer 2

SQL 的问题之一是它对完整性约束（尤其是引用约束）的支持水平很差。

出于所有实际目的，您的问题无法使用 SQL 约束来解决，除非您在想要向表中插入行时禁用约束。原因是 SQL 要求一次更新一个表，因此每当插入新行时都必须违反约束。这是 SQL 的一个基本限制，所有主要的 DBMS 都受到它的影响。

有一些解决方法，但没有一个是完美的。如果您的 DBMS 有 DEFERRABLE 约束（例如 Oracle），您可以使用 DEFERRABLE 约束。 DEFERRABLE 约束实际上只是禁用约束的一种简单方法。或者您可以使用触发器，这意味着规则是按程序强制执行的，而不是通过适当的数据库约束。

Answer 3

查看此线程：维护关系数据库中的子类完整性

该线程为 SQL Server 实现提供了多种建议，如果这些想法也不能应用于 Oracle，我会感到惊讶。

Answer 4

我知道这个问题是超类型/子类型问题。我已经在 SO 上写过好几次了。在这篇文章中，它是作为员工、客户和供应商问题的解决方案提出的。但是这篇文章对基本原理以及约束如何发挥作用进行了最广泛的讨论。它是根据在线出版物编写的。

Answer 5

在这种情况下，使用触发器对于强制执行这样的复杂约束是有价值的。

Answer 6

“假设我们想要强制执行
(1) 'gastropod' 中的每一行都与 'snail' 或 'slug' 中的一个对应行（但不是两者）
(2) 'slug' 中的每一行与 'gastropod' 中的每一行完全对应
(3) 'snail' 中的每一行在 'gastropod' 中都有一个对应的行”

(1) 是 'GASTROPOD' 和虚拟相关变量（又名“视图”）之间的包含依赖关系（又名“外键依赖关系”），定义为蛞蝓联盟蜗牛。
(2)和(3)是“SLUG”(/“SNAIL”)和“GASTROPOD”之间相同类型的包含依赖关系。

所有这些加在一起意味着“GASTROPOOD”和“SLUG UNION SNAIL”之间存在“平等依赖”（至少就标识符而言）。

请注意，为了能够更新处于此类约束下的数据库，您可能需要一个支持“多重分配”这一功能的 DBMS 引擎，或者一个支持“延迟约束检查”的 DBMS 引擎。

《数据库专业人员的应用数学》一书的第 11 章深入探讨了如何在 SQL 环境中实施此类约束（事实上，任何约束，无论多么复杂）。你的问题的答案几乎就是这一章的全部内容，我希望你不要指望我用几句话来概括它（答案的本质是“触发”——正如 StarShip3000 也指出的那样）。

Answer 7

我会选择

DROP TABLE GASTROPOD PURGE;
DROP TABLE SNAIL PURGE;

CREATE TABLE GASTROPOD
  (GASTROPOD_ID NUMBER,
  GASTROPOD_TYPE VARCHAR2(5),
  SNAIL_ID NUMBER,
  SLUG_ID NUMBER,
  CONSTRAINT GASTROPOD_PK PRIMARY KEY (GASTROPOD_ID),
  CONSTRAINT GASTROPOD_TYPE_CK CHECK (GASTROPOD_TYPE IN ('SLUG','SNAIL')),
  CONSTRAINT GASTROPOD_SLUG_CK CHECK 
     (SNAIL_ID IS NOT NULL OR SLUG_ID IS NOT NULL),
  CONSTRAINT GASTROPOD_SNAIL_CK1 CHECK 
     (GASTROPOD_TYPE = 'SNAIL' OR SLUG_ID IS NULL),
  CONSTRAINT GASTROPOD_SLUG_CK1 CHECK 
     (GASTROPOD_TYPE = 'SLUG' OR SNAIL_ID IS NULL),
  CONSTRAINT GASTROPOD_SNAIL_CK2 CHECK (SNAIL_ID = GASTROPOD_ID),
  CONSTRAINT GASTROPOD_SLUG_CK2 CHECK (SLUG_ID = GASTROPOD_ID),
  CONSTRAINT GASTROPOD_SNAIL_UK UNIQUE (SNAIL_ID),
  CONSTRAINT GASTROPOD_SLUG_UK UNIQUE (SLUG_ID)
  );

所以你检查腹足动物是蜗牛还是蛞蝓，并且设置了 slug_id 或 snail_id 。
如果是蜗牛，则 slug_id 必须为 null，对于 slug，则 snail_id 必须为 null。
确保 slug 和 snail id 是唯一的（我也添加了检查以将它们与 gastropod_id 相匹配）。

CREATE TABLE SNAIL
  (SNAIL_ID NUMBER,
   CONSTRAINT SNAIL_PK PRIMARY KEY (SNAIL_ID),
   CONSTRAINT SNAIL_FK FOREIGN KEY (SNAIL_ID)
     REFERENCES GASTROPOD (SNAIL_ID));

Snails 必须指向 gastropod 中 snail_id 不为 null 的行，并且它也是主键（因此是唯一的）。

ALTER TABLE GASTROPOD ADD CONSTRAINT SNAIL_GS_FK FOREIGN KEY (SNAIL_ID)
     REFERENCES SNAIL (SNAIL_ID) DEFERRABLE INITIALLY DEFERRED;

具有 snail_id 集的 Gastropod 也必须在 snail 中具有相应的行。我已经使这个方向可推迟，否则你将永远不会获得任何新数据。

Answer 8

外键引用来自 slug 和 snail 的腹足动物，并在外键列上使用唯一索引强制执行规则 2 和 3。规则 1 比较棘手:-(

我知道执行规则 1 的唯一方法是编写一些检查 snail 的数据库代码顺便说一句

，无论您按照什么顺序插入数据，都会违反规则。

Answer 9

“@Erwin，我更喜欢不涉及触发器的解决方案 - 我对它们有一种病态的厌恶。”

抱歉新答案，无权对此添加评论。

据我所知，由于您希望施加的约束的性质，在您的特定情况下，您可能能够摆脱“仅使用延迟约束”的问题。如果它适合你，并且你满意，那么一切都好，不是吗？

我的主要观点是，约束（例如：“作为数据库设计者可能遇到的任何可以想象的业务规则”）可以变得任意复杂。想象一个家谱数据库，您想要在其中强制执行“任何人都不能成为自己的祖先，无论在何种程度上”的规则（这是我最喜欢的例子，因为它最终涉及传递闭包和/或递归）。如果不使用触发器（或者顺便说一句，也不在触发器内使用递归 SQL），您就无法让 SQL DBMS 强制执行此类规则。

无论是你的 DBMS、我还是任何其他精通关系理论的人都不会关心你碰巧患有的任何病症。但也许由于您提到的这些病态，观察到您可以做所有您想做的事情，而无需定义任何触发器，如果​​您使用我自己开发的 DBMS（它确实支持类似触发器），这可能会很有趣的东西，但您不需要诉诸它们来强制数据完整性）。

Answer 10

所有这些例子对于一些简单的事情来说都具有极其复杂的程度：

create table gastropod(
    average_length numeric
);
create table slug(
    like gastropod,
    id          serial  primary key,
    is_mantle_visible boolean
);
create table snail(
    like gastropod,
    id          serial  primary key,
    average_shell_volume numeric
);   
\d snail;

        Column        |  Type   |                     Modifiers                      
----------------------+---------+----------------------------------------------------
 average_length       | numeric | 
 id                   | integer | not null default nextval('snail_id_seq'::regclass)
 average_shell_volume | numeric | 
Indexes:
    "snail_pkey" PRIMARY KEY, btree (id)

在你说这不是答案之前，请考虑一下需求。

1. “gastropod”中的每一行在“snail”或“slug”（但不是两者）中都有一个对应的
2. 行“slug”中的每一行在“gastropod”中恰好有一个对应的行
3. “snail”中的每一行都有一个对应的行在“gastropod”中，

表中包含该列就相当于数据完整性，没有任何废话。

注意：DDL 中的 LIKE 可以将所有列（甚至 9.0 中的约束和索引）复制到新表中。所以你可以做一些假继承。

Answer 11

这里有两个问题：

• 存在：父行不能没有至少一个子行。
• 排他性：父行不能包含多个子行。

在支持延迟约束的 DBMS（包括 PostgreSQL 和 Oracle）上，这两个目标都可以通过声明方式实现：

gastropod.snail_id 和 snail.snail_id 之间以及 gastropod.slug_id 和slug.slug_id。还有一个 CHECK 可确保其中一个完全匹配 gastropod.gastropod_id（另一个为 NULL）。

要在插入新数据时打破先有鸡还是先有蛋的问题，请推迟外键的一个方向。

以下是在 PostgreSQL 中的实现方式：

CREATE TABLE gastropod (
    gastropod_id int PRIMARY KEY,
    snail_id int UNIQUE,
    slug_id int UNIQUE,
    CHECK (
        (slug_id IS NULL AND snail_id IS NOT NULL AND snail_id = gastropod_id)
        OR (snail_id IS NULL AND slug_id IS NOT NULL AND slug_id = gastropod_id)
    )    
);

CREATE TABLE snail (
    snail_id int PRIMARY KEY,
    FOREIGN KEY (snail_id) REFERENCES gastropod (snail_id) ON DELETE CASCADE
);

CREATE TABLE slug (
    slug_id int PRIMARY KEY,
    FOREIGN KEY (slug_id) REFERENCES gastropod (slug_id) ON DELETE CASCADE
); 

ALTER TABLE gastropod ADD FOREIGN KEY (snail_id) REFERENCES snail (snail_id)
    DEFERRABLE INITIALLY DEFERRED;

ALTER TABLE gastropod ADD FOREIGN KEY (slug_id) REFERENCES slug (slug_id)
    DEFERRABLE INITIALLY DEFERRED;

新数据的插入方式如下：

START TRANSACTION;
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (1, 1);
INSERT INTO snail (snail_id) VALUES (1);
COMMIT;

但是，尝试仅插入父项而不插入子项失败：

START TRANSACTION;
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (2, 2);
COMMIT; -- FK violation.

插入错误类型的子项失败：

START TRANSACTION;
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (2, 2);
INSERT INTO slug (slug_id) VALUES (2); -- FK violation.
COMMIT;

插入设置太少、太多或不匹配的字段父级也会失败：

INSERT INTO gastropod (gastropod_id) VALUES (2); -- CHECK violation.
...
INSERT INTO gastropod (gastropod_id, snail_id, slug_id) VALUES (2, 2, 2); -- CHECK violation.
...
INSERT INTO gastropod (gastropod_id, snail_id) VALUES (1, 2); -- CHECK violation.

---

在不支持延迟约束的 DBMS 上，可以像这样以声明方式强制执行独占性（但不存在）：

在支持计算字段（例如 Oracle 11 虚拟列）的 DBMS 下，类型鉴别器 type< /code> 不需要物理存储在子表级别（仅父表）。

对于不支持键的 FK 引用超集的 DBMS 来说，唯一约束 U1 可能是必要的（据我所知，几乎所有这些），所以我们人为地制作了这个超级集。

---

这一切是否应该在实践中真正完成是另一回事。这是其中一种情况，在应用程序级别强制执行数据完整性的某些方面可能会通过减少开销和复杂性来证明是合理的。

Answer 12

理想情况下，我会创建一个带有“type”字段的表“gastropod”，然后有视图“gastropod”（选择除“type”之外的所有字段，没有“where”子句）、“snail”（使用“where” " 子句限制为 snail 类型）和“slug”（使用“where”子句限制为 slug 类型）。如果两种类型之一要小得多并且有许多字段仅与较小的类型相关，则可能存在异常，但在大多数情况下，使其与单个表不同的视图将确保适当的完整性约束。