如何在数据库中实现完全不相交的专业化？ [英] How to implement total disjoint specialization in database?

问题描述

说有一个学生和教授的数据库（一个非常简单的数据库），而关系数据库如下：

Say there is a database for students and professors (a very simple one) and the relational database is the following:

GradStudent (_id_, name, gradStuff)
UndergradStudent (_id_, name, underGradStuff)
Professor (_id_, name)
Teaches(_prof_id_, _stud_id_)

考虑到上面的关系数据库意在表示完全不相交的专业化，即没有表 Student 而是两个完全分开的，当用SQL编写此代码以实现数据库时，我将如何获取 Teaches 表的学生ID？
我无法弄清楚如何从两个不同的表中创建外键。

Considering that the relational database above is meant to represent total disjoint specialization, i.e. there is no table Student but rather two completely seperate ones, when writing this in SQL in order to implement the database, how would I fetch the student id for the Teaches table? I cannot figure out how to make a foreign key from two different tables.

我在编写此问题时假定SQL语言在所有方面都不相同所有平台。如果需要澄清：我正在使用Oracle SQL Developer。

I am writing this question assuming that the SQL language is not all the different across all the platforms. If clarification is needed: I am working on Oracle SQL Developer.

编辑 ::其他信息/说明：

Edit :: additional info / Clarification:

有关我要实现的目标的图形化，简单化视图：

For a more graphical, simplistic view on what I am trying to achieve:

我想用SQL代码编写以下内容（但是我不知道怎么可能，因此也不知道怎么做）。

I want to write the following in SQL code (however I do not know how is it possible and thus don't know how to)

如果图片过于简单，我道歉，如果需要，我可以添加更多属性和细节，请告诉我。

My apologies if the picture is too simplistic, if needed I can add more attributes and details, just let me know.

推荐答案

我不知道如何从两个不同的表中创建外键。

I cannot figure out how to make a foreign key from two different tables.

您的意思是指向/引用两个不同表的外键。但是在这种设计中没有这样的外键。

You mean, a foreign key to/referencing two different tables. But there is no such foreign key in this design.

我们为一个表声明一个SQL FOREIGN KEY，以表示（即告诉DBMS）a的值。列列表也是表（可能是同一表）中唯一的相应列的列表（可能是同一列表）的值。你这里没有这个。您对表有不同的约束。

We declare an SQL FOREIGN KEY for a table to say that (ie to tell the DBMS that) the values for a list of columns are also values of a list of corresponding columns (maybe the same list) that are unique in a table (maybe the same table). You don't have this here. You have a different constraint on your tables.

如果您想要这些基本表，则必须在SQL中使用触发器来强制执行约束。

If you want exactly those base tables then you have to use triggers in SQL to enforce your constraints.

您还可以使用以下设计：

You can also have a design with:

• 基表具有NOT NULL UNIQUE或PRIMARY KEY ID的学生


• 来自GradStudent（id），UndergradStudent（id）和Teaches（stud_id）的外键参考学生（id）


• 约束条件是id上的Student是GradStudent和UndergradStudent上id的投影的不相交的联合

您可以通过触发器来表达后者的约束。表达脱节（但不是联合）的无触发方式是：

You could express part the latter constraint by a trigger. A triggerless way to express the disjointedness (but not the union) is:

• 类型鉴别符/标签列 student_type （例如）在GradStudent，UndergradStudent&在GradStudent和UndergradStudent中具有其他FOREIGN（超级）键（id，student_type）到Student中的NOT NULL UNIQUE（id，student_type）的学生


• GradStudent CHECK（student_type ='grad'）和UndergradStudent CHECK（student_type ='undergrad'）

• a type discriminator/tag column student_type (say) in GradStudent, UndergradStudent & Student with additional FOREIGN (super) KEYs (id,student_type) from GradStudent and UndergradStudent to NOT NULL UNIQUE (id,student_type) in Student
• GradStudent CHECK( student_type = 'grad' ) and UndergradStudent CHECK ( student_type = 'undergrad' )

两个Student子类型基表中的行都相同（冗余）并且行Student中的ID由其ID（冗余）决定，但这是在没有触发器的情况下的成本。列Student_type可以是计算列。

Rows in each of the two student subtype base tables are all the same (redundancy) and rows in Student are determined by their id (redundancy) but that's the cost in this case of having no triggers. Column student_type could be a computed column.

实际上没有漂亮的SQL方法来强制每个父ID都是孩子。仅具有以上子表的LEFT JOIN而不是父表和子表会强制每个父表都是子表，但需要NULL列和更多约束。需要触发器来合理地约束SQL数据库。

There's really no pretty SQL way to enforce that every parent id is a child. Having only the LEFT JOIN of the above child tables instead of the parent and child tables enforces that every parent is a child but requires NULL columns and further constraints. One needs triggers to reasonably constrain SQL databases. One uses idioms to get what declarative constraints one can.

有关习惯用法的更多信息，请参见此答案及其链接。 Google的 stackoverflow数据库sql表，以及子级/父级，超级/子表，超级/子类型，继承和/或多态性。还有多个/许多/两个FK /关系/关联/引用/链接（尽管通常在此问题中，所需的约束不是 FK，而设计应使用子类型代替）。我用Google搜索了 stackoverflow两个外键，并得到了 this 。

For more on subtyping idioms see this answer and its links. Google 'stackoverflow database sql table' plus child/parent, super/subtables, super/subtypes, inheritance and/or polymorphism. Also multiple/many/two FKs/relationships/associations/references/links (although usually as in this question the constraint wanted is not a FK and the design should use subtypes instead). I googled "stackoverflow two foreign keys" and got this.

这篇关于如何在数据库中实现完全不相交的专业化？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！