在关系数据库中维护子类的完整性
假设我有一个代表超类的表，学生.然后我有 N 个表来表示该对象的子类(运动员、音乐家 等).我如何表达一个约束，使得学生必须在一个(不是更多，不是更少)子类中建模?
Let's say I have a table that represents a super class, students. And then I have N tables that represent subclasses of that object (athletes, musicians, etc). How can I express a constraint such that a student must be modeled in one (not more, not less) subclass?
关于评论的说明:
- 这是手动维护的，而不是通过 ORM 包.
- 与此相关的项目位于 SQL Server 之上(但很高兴看到通用解决方案)
- 这可能不是最好的例子.关于子类化，我们可以考虑几种情况，而我恰好发明了这个学生/运动员示例.
A) 在真正的面向对象方式中，超类可以单独存在并且不需要在任何子类中建模.
A) In true object-oriented fashion, it's possible that the superclass can exist by itself and need not be modeled in any subclasses.
B) 在现实生活中，任何物体或学生都可以有多个角色.
B) In real life, any object or student can have multiple roles.
C) 我试图说明的特定场景是要求每个对象都在一个子类中实现.将超类视为抽象实现，或者只是从其他不同的对象类/实例中分解出的共性.
C) The particular scenario I was trying to illustrate was requiring that every object be implemented in exactly one subclass. Think of the superclass as an abstract implementation, or just commonalities factored out of otherwise disparate object classes/instances.
感谢大家的投入，尤其是比尔.
Thanks to all for your input, especially Bill.
推荐答案
这里有几种可能性.一个是每个表中的 CHECK，student_id 没有出现在任何其他姐妹子类型表中.这可能很昂贵，并且每次需要新的子类型时，都需要修改所有现有表中的约束.
Here are a couple of possibilities. One is a CHECK in each table that the student_id does not appear in any of the other sister subtype tables. This is probably expensive and every time you need a new subtype, you need to modify the constraint in all the existing tables.
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).
edit: @JackPDouglas correctly points out that the above form of CHECK constraint is not supported by Microsoft SQL Server. Nor, in fact, is it valid per the SQL-99 standard to reference another table (see http://kb.askmonty.org/v/constraint_type-check-constraint).
SQL-99 为多表约束定义了元数据对象.这称为 ASSERTION，但我不知道任何实现断言的 RDBMS.
SQL-99 defines a metadata object for multi-table constraints. This is called an ASSERTION, however I don't know any RDBMS that implements assertions.
可能更好的方法是将students表中的主键设为复合主键，第二列表示子类型.然后将每个子表中的该列限制为与该表表示的子类型对应的单个值.无需将 PK 设为子表中的复合键.
Probably a better way is to make the primary key in the students table a compound primary key, the second column denotes a subtype. Then restrict that column in each child table to a single value corresponding to the subtype represented by the table. edit: no need to make the PK a compound key in child tables.
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 也可以很容易地是一个整数，我只是为了说明目的将它显示为一个字符.
Of course student_type could just as easily be an integer, I'm just showing it as a char for illustration purposes.
如果您不支持 CHECK 约束(例如 MySQL)，那么您可以在触发器中执行类似的操作.
If you don't have support for CHECK constraints (e.g. MySQL), then you can do something similar in a trigger.
我阅读了您关于确保超类表中的每一行在某些 子类表中都存在一行的后续文章.我不认为有一种实用的方法可以使用 SQL 元数据和约束来做到这一点.我建议满足此要求的唯一选择是使用 单表继承.否则，您需要依靠应用程序代码来强制执行.
I read your followup about making sure a row exists in some subclass table for every row in the superclass table. I don't think there's a practical way to do this with SQL metadata and constraints. The only option I can suggest to meet this requirement is to use Single-Table Inheritance. Otherwise you need to rely on application code to enforce it.
JackPDouglas 还建议使用基于 类表的设计继承.见 他的示例 或我的类似技术示例 此处 或此处 或此处.
edit: JackPDouglas also suggests using a design based on Class Table Inheritance. See his example or my examples of the similar technique here or here or here.
相关文章