database 数据库属性可以是主键和外键吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/9244432/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Can a database attribute be primary and foreign key?
提问by RichardG
I have 2 tables, Userand Employee. Each user is given a User_IDand that is a primary key in the Usertable and a foreign key in the Employeetable. Can that attribute in the Employeetable also be a primary key?
我有 2 张桌子，User和Employee. 每个用户都被赋予一个User_ID，它是User表中的主键和表中的外键Employee。Employee表中的那个属性也可以是主键吗？
回答by Olivier Jacot-Descombes
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 Userand Employee, then yes, the User_IDin the Employeetable 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_IDis FK and PK in Employee, each user can have at most one employee record attached. If User_IDwas only FK but not PK in table Employeethen a user could have several related employee records.
在你的情况，如果有一个一对一或一对零或一的关系User和Employee，然后是的，User_ID在Employee表可以是外键（FK）和主键（PK）的同时。换句话说，这意味着：用户也可以是员工，在这种情况下，员工数据将附加到用户身上。如果他不是员工（他可以是外部专家），则不会附加任何员工记录。如果User_ID是 FK 和 PK in Employee，每个用户最多可以附加一个员工记录。如果表User_ID中只有 FK 而不是 PK，Employee那么用户可以有多个相关的员工记录。
回答by Neil
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.
否则，您通常不会拥有与外键相同的主键，尽管它可能包含外键，就像多对多关系的联结表的情况一样。
