数据库属性可以是主键还是外键？

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?

我有2个表，User和Employee。每个用户都被赋予User_ID，这是User表中的主键和Employee表中的外键。 Employee表中的该属性也可以是主键吗？

2 个解决方案

#1

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. |
+----------+     +-------------+

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和n表的两个主键相关的列。这些列同时是主键和外键。

                    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.

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

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.

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

#2

Yes. You would do this for instance if you wanted to enforce that all employees are users, and some users can be employees. This would be (zero or one) to one relationship.

是。例如，如果您想强制所有员工都是用户，而某些用户可以是员工，那么您可以这样做。这将是（零或一）到一个关系。

Otherwise, you would not normally have the primary key the same as the foreign key, although it could contain foreign key(s), as in the case of a junction table for a many to many relationship.

否则，您通常不会将主键与外键相同，尽管它可能包含外键，就像多表关系的联结表一样。

#1