维护关系数据库中的子类完整性 [英] Maintaining subclass integrity in a relational database

问题描述

假设我有一个表，代表一个超级类，学生。然后我有N个表格表示该对象的子类（运动员，音乐家等）。如何表达一个限制，以便学生必须在一个（不是更多，而不是更少）的子类中建模？

关于意见的澄清：

• 这是手动维护的，而不是通过ORM包。


• 与SQL Server相关的项目（但它很高兴看到一个通用的解决方案）


• 这可能不是最好的例子。有一些情况我们可以考虑到子类化，我刚刚发明了这个学生/运动员的例子。

A）面向对象的时尚，超类可以自己存在，不需要在任何子类中建模。

B）在现实生活中，任何对象或学生都可以拥有多个角色。

C）我试图说明的特定场景是要求每个对象都在一个子类中实现。将超类视为抽象实现，或者仅仅是将其他不同的对象类/实例的共同因素。

感谢所有的输入，特别是Bill。

解决方案

这里有几种可能性。每个表中的一个是 CHECK ， student_id 不会出现在任何其他姊妹子类型表中。这可能很贵，每次需要新的子类型时，您需要修改所有现有表中的约束。

  CREATE TABLE运动员（
 student_id INT NOT NULL PRIMARY KEY，
 FOREIGN KEY（student_id）REFERENCES学生（student_id），
 CHECK（student_id NOT IN（SELECT student_id FROM musicians 
 UNION SELECT student_id从懒人
 UNION ...））
）; 
  

编辑： @JackPDouglas正确指出上述形式的CHECK约束Microsoft SQL Server不支持。实际上，根据SQL-99标准也可以引用另一个表（参见 http://kb.askmonty.org/v/constraint_type-check-constraint ）。

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

可能更好的方法是使主键在学生表中复合主键，第二列表示子类型。然后将每个子表中的列限制为与表所表示的子类型对应的单个值。 编辑：不需要使PK成为子表中的复合键。

  CREATE TABLE运动员
 student_id INT NOT NULL PRIMARY KEY 
 student_type CHAR（4）NOT NULL CHECK（student_type ='ATHL'），
 FOREIGN KEY（student_id，student_type）参考学生（student_id，student_type）
）; 
  

当然 student_type 可以很容易一个整数，我只是把它显示为一个字符为了说明的目的。

如果你不支持 CHECK 约束（例如MySQL），那么你可以在触发器中做类似的事情。

我阅读了关于确保一行存在于 / em>超类表中的每一行的子类表。我不认为有一个实用的方式来做到这一点与SQL元数据和约束。我可以建议满足此要求的唯一选项是使用单表继承。否则，您需要依赖应用程式代码强制执行。

编辑： JackPDouglas还建议使用基于类表继承。请参阅他的示例或类似技术的示例 here 或这里或 here 。

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?

Clarifications regarding comments:

• This is being maintained manually, not through an ORM package.
• The project this relates to sits atop SQL Server (but it would be nice to see a generic solution)
• This may not have been the best example. There are a couple scenarios we can consider regarding subclassing, and I just happened to invent this student/athlete example.

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) In real life, any object or student can have multiple roles.

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.

解决方案

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

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 defines a metadata object for multi-table constraints. This is called an ASSERTION, however I don't know any RDBMS that implements assertions.

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

Of course student_type could just as easily be an integer, I'm just showing it as a char for illustration purposes.

If you don't have support for CHECK constraints (e.g. MySQL), then you can do something similar in a trigger.

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.

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.

这篇关于维护关系数据库中的子类完整性的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！