Oracle 中的外键约束

Question

我有实体关系模型 (ERD)，其中实体 IndividualCategory 和 TeamCategory 与实体 Category 相关。现在我想在 Oracle DB 中创建表。我是这样开始的：

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

外键和主键约束的组合确保对于每个Individual_category在Category中都会有相应的记录 “超级”表（或“父”表？）。对于特定的Category 记录，只有一个IndividualCategory 记录。 Team_category 也是如此。

为了强制继承，我还需要一个约束：一个约束，确保对于 Category 中的每条记录，IndividualCategory (X) 中都会有一个记录，或者 中会有一个记录TeamCategory 但不是两者。

我如何创建这样的约束？

---

编辑：这就是我所说的“ER 模型中的继承”的意思。这是来自我的数据库老师的幻灯片（他们在那里称之为“实体子类型”，但有时他们称之为继承）：

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

确保类别是 TeamCategory x 或 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)
  );

创建可延迟完整性约束，以便可以在同一事务中插入类别和 Team/Individual_Category；否则，您无法在 TeamCategory/IndividualCategory 之前插入类别，反之亦然。第 22 条军规。

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;

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 触发器，以便应用程序看起来像是一张表和其他人一样。

Answer 3

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

对于此示例，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 刷新的开销不会太高。

Answer 4

ERD 继承是 gen-spec 设计模式的经典示例。有很多关于如何在 Oracle 等关系 DBMS 中设计 gen-spec 的文章。你可以通过谷歌搜索“泛化专业化关系建模”找到其中一些。

您将在这些文章中看到的大部分内容已经在对此问题的其他答复中概述。这个话题在《SO》中已经多次出现。有关先前讨论的示例，单击此处。

经典解决方案的主要特点是专用表有一个 id 列，它既是主键又是引用通用表 id 列的外键。以这种方式，子实体不会获得它们自己的身份。您真正需要注意的功能是实现析取的约束。并非所有文章都在其提出的解决方案中强制执行此规则。