外键是指跨多个表的主键? [英] Foreign key referring to primary keys across multiple tables?

问题描述

我有两个表，即employees_ce 和employees_sn 数据库下的employees.

I have to two tables namely employees_ce and employees_sn under the database employees.

它们都有各自唯一的主键列.

They both have their respective unique primary key columns.

我有另一个名为 deductions 的表，我想引用它的外键列来引用employees_ce 和employees_sn 的主键.这可能吗?

I have another table called deductions, whose foreign key column I want to reference to primary keys of employees_ce as well as employees_sn. Is this possible?

例如

employees_ce
--------------
empid   name
khce1   prince

employees_sn
----------------
empid   name
khsn1   princess

这可能吗?

deductions
--------------
id      name
khce1   gold
khsn1   silver

推荐答案

假设我已经正确理解了你的场景，这就是我所说的正确方法:

Assuming that I have understood your scenario correctly, this is what I would call the right way to do this:

从更高级别的数据库描述开始！您有员工，员工可以是ce"员工和sn"员工(无论是什么).在面向对象的术语中，有一个类employee"，它有两个子类，分别称为ce employee"和sn employee".

Start from a higher-level description of your database! You have employees, and employees can be "ce" employees and "sn" employees (whatever those are). In object-oriented terms, there is a class "employee", with two sub-classes called "ce employee" and "sn employee".

然后您将这个更高级别的描述转换为三个表:employees、employees_ce 和 employees_sn:

Then you translate this higher-level description to three tables: employees, employees_ce and employees_sn:

• 员工(身份证、姓名)
• employees_ce(id, ce-specific stuff)
• employees_sn(id, sn-specific stuff)

由于所有员工都是员工(呵呵！)，每个员工在 employees 表中都会有一行.ce"员工在 employees_ce 表中也有一行，sn"员工在 employees_sn 表中也有一行.employees_ce.id 是 employees.id 的外键，就像 employees_sn.id 一样.

Since all employees are employees (duh!), every employee will have a row in the employees table. "ce" employees also have a row in the employees_ce table, and "sn" employees also have a row in the employees_sn table. employees_ce.id is a foreign key to employees.id, just as employees_sn.id is.

要提及任何类型的员工(ce 或 sn)，请参阅 employees 表.也就是说，您遇到问题的外键应该引用该表！

To refer to an employee of any kind (ce or sn), refer to the employees table. That is, the foreign key you had trouble with should refer to that table!

这篇关于外键是指跨多个表的主键?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！