Oracle中的外键约束

Question

我有一个实体关系模型（ERD），其中的实体IndividualCategory和TeamCategory与实体Category相关联。现在我想在Oracle数据库中创建表。我开始的方式如下：

CREATE TABLE Category(
    category_id INT PRIMARY KEY,
    ...
);

CREATE TABLE Individual_category(
    category_id INT CONSTRAINT fk_cat_indivcat REFERENCES Category(category_id),
    ...,
    CONSTRAINT pk_indivgamecat PRIMARY KEY (category_id)
);

CREATE TABLE Team_category(
    category_id INT CONSTRAINT fk_cat_teamcat REFERENCES Category(category_id),
    ...,
    CONSTRAINT pk_teamcat PRIMARY KEY (category_id)
);

这个 Foreign key 和 Primary key 的组合保证了每个 Individual_category 都有对应的 Category "super" 表（或者 "parent" 表？）。并且每个特定的 Category 记录只有一个 IndividualCategory 记录。同样适用于 Team_category。

为了强制实现继承，我需要一个额外的约束条件：确保每个 Category 记录要么有一个 IndividualCategory 记录(X)，要么有一个 TeamCategory 记录，但不会同时拥有两者。

如何创建这样的约束条件？

---

编辑： 我所说的 "E-R 模型中的继承" 如下图所示，来自我的数据库老师的幻灯片（他们称之为 "Entity sub-type"，但有时仅称其为继承）：

- Rasto

---

“继承”意味着层次化数据，但此示例并未表明。它仅使用外键... - OMG Ponies

@OMG Ponies：我不明白你的意思...确实，在所有三个表中都有更多的列。并且Category的所有列都应该被继承到两个子表中。但是怎样做呢？这就是问题所在。 - Rasto

你的术语会阻碍你找到所需的答案。与对象的“继承”不同，外键仅验证列中的值是否已存在于约束所涉及的表.列中。 - OMG Ponies

@OMG Ponies：+1 我有一种感觉，我的问题可能是术语方面出了问题...好的，让我们用非常简单的话来解释我的问题：我被分配了一个E-R模型作业。在Category（父级）和IndividualCategory、TeamCategory（子级）之间存在继承关系。Category实体具有一些属性（例如：category_name）。IndividualCategory和TeamCategory都会从Category中添加一些内容。我必须编写SQL以创建与E-R模型中的实体相对应的表。我如何处理这种继承，以便我的... - Rasto

“实体”和“表”都是ER模型的组成部分 - “实体”是逻辑版本，“表”是物理版本。 物理版本处理实际列定义和数据类型大小问题。 逻辑仅涉及实体（通常转换为表）和它们之间的关系。 但在我遇到的任何数据库设计/建模中都没有继承关系/约束 - 只有外键，以展示父子关系。 - OMG Ponies

Answer 1

使用可延迟约束条件来完全不同地实现此操作：

CREATE TABLE Category(
    category_id INT PRIMARY KEY,
    team_category_id INT,
    individual_category_id INT,
    ...
);

CREATE TABLE Individual_category(
    individual_category_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    ...,
);

CREATE TABLE Team_category(
    team_category_id INT PRIMARY KEY,
    category_id INT NOT NULL,
    ...,
);

请确保 Category 是 TeamCategory 或 IndividualCategory 中的一个：

alter table Category add constraint category_type_check check
  (   (team_category_id is null and individual_category_id is not null)
   or (team_category_id is not null and individual_category_id is null)
  );

创建可延迟完整性约束，以便可以在同一事务中插入类别和团队/个人类别；否则，无法在TeamCategory/IndividualCategory之前插入类别，反之亦然。这是一个进退两难的局面。

alter table category add constraint category_team_fk 
  foreign key (team_category_id)
    references team_category (team_category_id) 
    deferrable initially deferred;

alter table category add constraint category_individual_fk 
  foreign key (individual_category_id)
    references individual_category (individual_category_id) 
    deferrable initially deferred;

alter table individual_category add constraint individual_category_fk
  foreign_key (category_id) 
  references category (category_id)
  deferrable initially deferred;

alter table team_category add constraint team_category_fk
  foreign_key (category_id) 
  references category (category_id)
  deferrable initially deferred;

- Adam Musch

---

到目前为止，这似乎是我想要做的最好的方式。 - Rasto

这可能是教授想要的，但在现实世界中，要定义类别，你必须始终加入那些表，或者使用视图 - 而前面的例子总是有视图，这个解决方案更加复杂，实际使用起来更加困难。 - Adam Musch

这是一个强有力的尝试来解决一个“经典”的问题，但是Category中的可空子类型键让我很困扰。当然，问题在于SQL（标准和实现）缺乏必要的例如CREATE ASSERTION和CHECK约束，这些约束可以包含子查询、多重赋值和理想情况下的“不相交”约束语法（所谓的“外部分布键”等）。 - onedaywhen

为什么那些可空列让你烦恼？顺便说一下，这不是我在现实世界中实现它的方式 - 这是对问题的学术回答。 - Adam Musch

Answer 2

如何实现这个目标，可以使用一个简化的例子来说明：

假设我们有一个包含数字的列表：

CREATE TABLE Category(
    category_id INT PRIMARY KEY,
    category_type varchar2(300) not null,
    ...
    [list of required attributes for only individual category, but nullable],
    [list of required attributes for only team category, but nullable]
);

alter table category add constraint check_category_individual check
  (   category_type <> 'INDIVIDUAL' 
   or (    category_type = 'INDIVIDUAL' 
       and [list of individual category attributes IS NOT NULL]
      )
  );

alter table category add constraint check_category_team check
  (   category_type <> 'TEAM' 
   or (    category_type = 'TEAM' 
       and [list of team category attributes IS NOT NULL]
      )
  );

您可以创建以下视图：

create view individual_category as
select category_id, [base category attributes], [individual category attributes]
  from category
 where category_type = 'INDIVIDUAL;

你甚至可以在视图上放置一个INSTEAD OF触发器，这样它就会像其他任何表一样出现在应用程序中。

- Adam Musch

Answer 3

ERD继承是通用-特殊设计模式的经典示例。有许多关于如何在关系型数据库管理系统（如Oracle）中设计通用-特殊的文章。您可以通过在Google上搜索“generalization specialization relational modeling”来找到其中一些。

这些文章中的许多内容已经在其他回答此问题的响应中概述过了。这个主题在SO上出现了很多次。有一个以前的讨论样本，点击这里。

经典解决方案的主要特点是专用表具有一个id列，该列既是主键又是外键，引用了广义表的id列。通过这种方式，子实体不会获得自己的标识。您真正需要注意的功能是实现分离的约束条件。并非所有文章都在其呈现的解决方案中强制执行此规则。

- Walter Mitty

Answer 4

在数据库中实现复杂约束的另一种方法是使用物化视图（MVs）。

对于这个例子，可以定义一个MV如下：

create materialized view bad_category_mv
refresh complete on commit 
as
select c.category_id
from category c
left outer join individual_category i on i.category_id = c.category_id
left outer join team_category i on t.category_id = c.category_id
where (  (i.category_id is null and t.category_id is null)
      or (i.category_id is not null and t.category_id is not null)
      );

alter table bad_category_mv
add constraint bad_category_mv_chk
check (1=0) deferrable;

因此，MV仅针对违反规则的类别进行填充，但是检查约束条件确保任何导致MV中出现行的事务都会失败（因为1=0永远不成立）。

我曾经在这里博客中介绍过这种方法。

注意：尽管我对这种方法很感兴趣，但我从未在生产数据库中使用过它。需要仔细的基准测试来确保每当数据发生更改时，完整的MV刷新的开销不会太高。

- Tony Andrews