维护关系数据库中的子类完整性

每个Student记录都有一个SubClass列(为了便于讨论,假设它是CHAR(1))。{A=运动员,M=音乐家…}

现在创建运动员和音乐家表。它们还应该有一个子类列,但应该有一个检查约束,对它们所代表的表类型的值进行硬编码。例如,您应该为Athlete表的SubClass列设置默认值“a”和CHECK约束“a”。

使用StudentID and子类的COMPOSITE外键将音乐家和运动员表链接到学生表。你完了!去喝一杯好咖啡吧。

CREATE TABLE Student (
    StudentID INT NOT NULL IDENTITY PRIMARY KEY,
    SubClass CHAR(1) NOT NULL,
    Name VARCHAR(200) NOT NULL,
    CONSTRAINT UQ_Student UNIQUE (StudentID, SubClass)
);

CREATE TABLE Athlete (
    StudentID INT NOT NULL PRIMARY KEY,
    SubClass CHAR(1) NOT NULL,
    Sport VARCHAR(200) NOT NULL,
    CONSTRAINT CHK_Jock CHECK (SubClass = 'A'),
    CONSTRAINT FK_Student_Athlete FOREIGN KEY (StudentID, Subclass) REFERENCES Student(StudentID, Subclass)
);

CREATE TABLE Musician (
    StudentID INT NOT NULL PRIMARY KEY,
    SubClass CHAR(1) NOT NULL,
    Instrument VARCHAR(200) NOT NULL,
    CONSTRAINT CHK_Band_Nerd CHECK (SubClass = 'M'),
    CONSTRAINT FK_Student_Musician FOREIGN KEY (StudentID, Subclass) REFERENCES Student(StudentID, Subclass)
);

以下是几种可能性。一个是a CHECK 在每张桌子上 student_id 未出现在任何其他姐妹子类型表中。这可能很昂贵,每次需要新的子类型时,都需要修改所有现有表中的约束。

CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  FOREIGN KEY (student_id) REFERENCES students(student_id),
  CHECK (student_id NOT IN (SELECT student_id FROM musicians 
                      UNION SELECT student_id FROM slackers 
                      UNION ...)) 
);

编辑: @JackPDouglas正确地指出,Microsoft SQL Server不支持上述形式的CHECK约束。事实上,根据SQL-99标准,引用另一个表也是无效的(参见 http://kb.askmonty.org/v/constraint_type-check-constraint ).

SQL-99为多表约束定义了一个元数据对象。这被称为 断言 ,但是我不知道任何实现断言的RDBMS。

也许更好的方法是在 students 表中的复合主键,第二列表示子类型。然后将每个子表中的该列限制为与表表示的子类型对应的单个值。 编辑: 无需在子表中将PK设置为复合键。

CREATE TABLE athletes (
  student_id INT NOT NULL PRIMARY KEY,
  student_type CHAR(4) NOT NULL CHECK (student_type = 'ATHL'),
  FOREIGN KEY (student_id, student_type) REFERENCES students(student_id, student_type)
);

当然 student_type 也可以很容易地是一个整数,我只是出于说明的目的将其显示为char。

如果你没有支持 检查 约束(例如MySQL),然后您可以在触发器中执行类似的操作。

我读过你关于确保在 一些 子类表用于超类表中的每一行。我认为没有一种实用的方法可以用SQL元数据和约束来实现这一点。我唯一能建议满足这一要求的选择是使用 Single-Table Inheritance 。否则,您需要依靠应用程序代码来执行它。

编辑: JackPDouglas还建议使用基于 Class Table Inheritance 。参见 his example 或者我举的类似技术的例子 here 或 here 或 here .

如果你对数据建模感兴趣,除了对象建模,我建议你在网上查找“关系建模泛化专业化”。

过去有一些很好的资源可以很好地解释这种模式。

我希望这些资源仍然存在。

这是我希望你能找到的简化视图。

在开始设计数据库之前,最好提出一个概念数据模型,将数据库中存储的值连接回主题。创建概念数据模型实际上是数据分析,而不是数据库设计。有时很难将分析和设计分开。

在概念层面建模数据的一种方法是实体关系(ER)模型。对专业化泛化情况进行建模有众所周知的模式。将这些ER模式转换为SQL表(称为逻辑设计)非常简单,尽管您必须做出一些设计选择。

如果我没理解错的话,你给出的一个学生可能扮演音乐家等多种角色的案例可能并不能说明你感兴趣的案例。您似乎对子类互斥的情况感兴趣。也许车辆可能是汽车、卡车或摩托车的情况可能更容易讨论。

您可能会遇到的一个区别是,超类的通用表并不真正需要类型代码列。单个超类实例的类型可以通过各种子类表中是否存在外键来推断。包含或省略类型代码是否更明智取决于您打算如何使用数据。

有趣的问题。当然,子表有FK约束,所以这些子表必须有一个学生。

主要问题是在插入时试图进行检查。必须先插入学生,这样你就不会违反子表中的FK约束,这样做检查的触发器就不会起作用。

你可以编写一个应用程序,不时检查你是否真的对此感到担忧。我认为最大的恐惧是删除。有人可以删除子表条目,但不能删除学生。您可以设置触发器来检查何时从子表中删除项目,因为这可能是最大的问题。

我也有一个数据库,每个子类层次结构都有一个表,就像这样。我正确使用Hibernate及其映射,因此它会自动删除所有内容。如果手动执行此操作,那么我会确保始终删除具有适当级联的父级hehe:)

谢谢你,比尔。你让我想起来了。..

超类表有一个子类代码列。每个子类表都有一个外键约束,以及一个指示id与超类表的子集(其中code=athlete)一起存在的约束。

这里唯一缺少的部分是,可以在没有子类的情况下对超类进行建模。即使您将代码列设置为必填项,它也可能只是一个空连接。这可以通过添加一个约束来解决,即超类的id存在于子类表中id的联合中。如果在事务中间强制执行约束,那么这两个约束的插入会有点麻烦。或者,不要担心未分类的对象。

编辑:哇,听起来真是个好主意。..但由于不支持引用其他表的子查询,因此受到阻碍。至少在SQL Server中没有。

这可以通过添加一个约束来解决,即超类的id存在于 子类表中的id。

根据你想在模式中投入多少智能(以及MS SQL Server允许你投入多少),你实际上不需要对子类表进行联合,因为你知道,如果id存在于任何子类表中,它必须存在于与子类代码列标识的子类相同的子类中。

我可能会添加一个检查约束。
将外键创建为可空。 添加一个检查,以确保它们不都为空,并确保它们都没有设置。 约束[CK_HasOneForiegnKey]检查((FK_First!=NULL或FK_Second!=NULL)和非(FK_Ffirst!=NULL和FK_Seconds!=NULL。

我不确定,但我相信这将允许您一次只设置一个密钥。