sql - 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中会有相应的记录“ super ”表(或“父”表？)。并且只有一个 IndividualCategory特定记录 Category记录。与 Team_category 相同.

为了强制继承，我还需要一个约束:一个约束，确保对 Category 中的每条记录。 IndividualCategory中将有记录(X)OR 记录 TeamCategory但不是两者兼而有之。

我如何创建这样的约束？

编辑:这就是我所说的“E-R 模型中的继承”。这是我的数据库老师的幻灯片(他们在那里称其为“实体子类型”，但有时仅称其为继承):

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 xor 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 之前插入 Category，反之亦然。一个 catch-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;