如何在数据库中实现完全分离专门化?

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

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中编写这个时，我如何为teach表获取学生id ?我不知道如何从两个不同的表中创建一个外键。

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.

我写这个问题的前提是SQL语言并不是在所有平台上都是不同的。如果需要澄清:我正在开发Oracle SQL Developer。

Edit :: additional info / Clarification:

编辑::补充信息/澄清:

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

对于我想要达到的目标，有一个更简单、更简单的观点:

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)

我想在SQL代码中编写以下代码(但是我不知道怎么可能，因此不知道如何)

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

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

2 个解决方案

#1

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.

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

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外键来声明(即告诉DBMS)列列表的值也是表中唯一的对应列(可能是同一个列表)的值。这里没有这个。表上有不同的约束。

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

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

You can also have a design with:

你也可以设计一个:

• base table Student with NOT NULL UNIQUE or PRIMARY KEY id
• 具有非空唯一或主键id的基表学生
• FOREIGN KEYs from GradStudent (id), UndergradStudent (id) and Teaches (stud_id) REFERENCES Student (id)
• 学生(id)、学生(id)和教师(stud_id)的外钥参考学生(id)
• a constraint that the projection of Student on id is the disjoint union of the projections of GradStudent and UndergradStudent on id
• 学生在id上的投影是小学生和小学生在id上投影的不相交结合

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

可以用触发器来表示后一个约束。表达这种不愉快(但不是工会)的一种无触发方式是:

• 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
• 一种类型鉴别器/标记列student_type(例如)在小学生、小学生和学生中带有额外的外国(超级)键(id,student_type)，从小学生到小学生的非空唯一(id,student_type)
• GradStudent CHECK( student_type = 'grad' ) and UndergradStudent CHECK ( student_type = 'undergrad' )
• 学生检查(student_type = 'grad')和低年级学生检查(student_type = 'undergrad')

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.

两个student子类型基表中的每一行都是相同的(冗余)，而student中的行是由它们的id(冗余)决定的，但在这种情况下，没有触发器是要付出代价的。列student_type可以是一个计算列。

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.

没有很好的SQL方法来强制每个父母id都是孩子。只有上面的子表的左连接，而不是父表和子表的左连接，就意味着每个父表都是子表，但需要空列和进一步的约束。一个需要触发器来合理地约束SQL数据库。我们可以使用习语来获取声明性约束。

For more on subtyping idioms see this answer and its links. Google '* 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 "* two foreign keys" and got this.

有关子类型习语的更多信息，请参见此答案及其链接。谷歌'*数据库sql表'加上子/父、超/子表、超/子类型、继承和/或多态性。还有多个/多个/两个FKs/关系/关联/引用/链接(尽管在这个问题中，通常需要的约束不是FK，设计应该使用子类型)。我用谷歌搜索了“*两个外键”，得到了这个。

#2

If by "fetch the student id for the Teaches table", you mean you want Teaches.stud_id to be a FK that references "GradStudent or Undergradstudent as is the case", you can't. The target of a FK must be a key of a table that is not a view. You have no such table, ergo you have no such key either.

如果“获取教案的学生id”，你的意思是你想要教案。stud_id指的是一个FK，它引用了“小学生或小学生的情况”，你不能。FK的目标必须是不是视图的表的键。你没有这样的桌子，所以你也没有这样的钥匙。

Only way I see is to code a trigger that does the check upon inserts/updates to Teaches.

我所看到的唯一的方法是编写一个触发器，它对插入/更新进行检查。