数据库属性可以是主键和外键吗? [英] Can a database attribute be primary and foreign key?

问题描述

我有 2 个表，User 和 Employee.每个用户都有一个 User_ID，它是 User 表中的主键和 Employee 表中的外键.Employee 表中的那个属性也可以是主键吗?

I have 2 tables, User and Employee. Each user is given a User_ID and that is a primary key in the User table and a foreign key in the Employee table. Can that attribute in the Employee table also be a primary key?

推荐答案

如果两个表之间是一对一的关系，那么明细表的主键也是外键.

If you have a one-to-one relation between two tables, then the primary key of the details table is a foreign key as well.

 master           detail (1 : 1)
+----------+ 1:1 +-------------+
| PK  id   |<---o| PK FK  id   |
+----------+     +-------------+
|     col1 |     |        col1 |
|     col2 |     |        col2 |
|     etc. |     |        etc. |
+----------+     +-------------+

<小时>

如果您有 m 对 n 关系，则联结表具有与 m 表和 n 表的两个主键相关的列.这些列同时是主键和外键.

---

If you have a m-to-n relation, the junction table has columns relating to the two primary keys of the m and the n-tables. These columns are primary keys and foreign keys at the same time.

                    m : n
 m_table          junction
+----------+ 1:m +------------+      n_table
| PK  id1  |<---o| PK FK1 id1 | n:1 +----------+
+----------+     | PK FK2 id2 |o--->| PK  id2  |
|     col1 |     +------------+     +----------+
|     col2 |     |            |     |     col1 |
|     etc. |     +------------+     |     etc. |
+----------+                        +----------+

注意，使用这种结构，一个表的记录只能链接到另一个表的特定记录一次，因为联结表的每个复合主键必须是唯一的.如果要允许非唯一配对，请在联结表中定义一个单独的主键:

Note that with this construction, a record of one table can only be linked to a specific record of the other table once, since each composite primary key of the junction table must be unique. If you want to allow non-unique pairings, define a separate primary key in the junction table:

                    m : n
                  junction
                 +---------+
 m_table         | PK  id  |
+----------+ 1:m +---------+      n_table
| PK  id1  |<---o| FK1 id1 | n:1 +----------+
+----------+     | FK2 id2 |o--->| PK  id2  |
|     col1 |     |         |     +----------+
|     col2 |     +---------+     |     col1 |
|     etc. |                     |     etc. |
+----------+                     +----------+

在这种情况下，主键和外键约束设置在不同的列上.或者，您也可以使用两个外键加上一个分子或另一个识别属性来构建主键.

In this case, the primary key and foreign key constraints are set on different columns. Alternatively you can also build the primary key with the two foreign keys plus one numerator or another discerning attribute.

在您的情况下，如果 User 和 Employee 之间存在一对一或一对零或一对一的关系，那么是的，Employee 表中的User_ID 可以同时是外键(FK)和主键(PK).换句话说，这意味着:用户也可以是员工，在这种情况下，员工数据将附加到用户.如果他不是员工(他可能是外部专家)，则不会附加员工记录.如果User_ID是Employee中的FK和PK，则每个用户最多可以附加一个员工记录.如果 User_ID 在表 Employee 中只是 FK 而不是 PK，那么一个用户可能有多个相关的员工记录.

In your case, if there is a one-to-one or a one-to-zero-or-one relationship between User and Employee, then yes, the User_ID in the Employee table can be Foreign Key (FK) and Primary Key (PK) at the same time. In words, this would mean: A user can be an employee as well, in which case the employee data would be attached to the user. If he is not an employee (he could be an external expert), no employee record is attached. If User_ID is FK and PK in Employee, each user can have at most one employee record attached. If User_ID was only FK but not PK in table Employee then a user could have several related employee records.

这篇关于数据库属性可以是主键和外键吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！