MySQL 外键可以作为主键吗？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17636106/
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 foreign key act as a primary key?
提问by christianleroy
I'm currently designing a database structure for our team's project. I have this very question in mind currently: Is it possible to have a foreign key act as a primary key on another table?
我目前正在为我们团队的项目设计数据库结构。我目前正在考虑这个问题：是否可以将外键用作另一个表上的主键？
Here are some of the tables of our system's database design:
以下是我们系统数据库设计的一些表：
user_accounts
students
guidance_counselors
What I wanted to happen is that the user_accountstable should contain the IDs (supposedly the login credential to the system) and passwords of both the student users and guidance counselor users. In short, the primary keys of both the studentsand guidance_counselorstable are also the foreign key from the user_accountstable. But I am not sure if it is allowed.
我想要发生的是该user_accounts表应该包含学生用户和指导顾问用户的 ID（据说是系统的登录凭据）和密码。简而言之，students和guidance_counselors表的主键也是表中的外键user_accounts。但我不确定是否允许。
Another question is: a student_rectable also exists, which requires a student_number(which is the user_idin the user_accountstable) and a guidance_counsellor_id(which is also the user_idin the user_accounts) for each of its record. If both the IDs of a student and guidance counselor come from the user_accounts table, how would I design the student_rectable? And for future reference, how do I manually write it as an SQL code?
另一个问题是：一个student_rec表中也存在，这需要一个student_number（其为user_id中user_accounts表）和一个guidance_counsellor_id（这也是user_id在user_accounts）它的每个记录的。如果学生和辅导员的 ID 都来自user_accounts table，我将如何设计student_rec表格？为了将来参考，我如何手动将其编写为 SQL 代码？
This has been bugging me and I can't find any specific or sure answer to my questions.
这一直困扰着我，我找不到任何具体或确定的答案来回答我的问题。
回答by David
Of course. This is a common technique known as supertypingtables. As in your example, the idea is that one table contains a superset of entities and has common attributes describing a general entity, and other tables contain subsets of those entities with specific attributes. It's not unlike a simple class hierarchy in object-oriented design.
当然。这是一种称为超类型表的常用技术。在您的示例中，想法是一个表包含实体的超集并具有描述一般实体的公共属性，而其他表包含具有特定属性的那些实体的子集。它与面向对象设计中的简单类层次结构没有什么不同。
For your second question, one table can have two columns which are separately foreign keys to the same other table. When the database builds the query, it joins that other table twice. To illustrate in a SQL query (not sure about MySQL syntax, I haven't used it in a long time, so this is MS SQL syntax specifically), you would give that table two distinct aliases when selecting data. Something like this:
对于您的第二个问题，一个表可以有两列，它们分别是另一个表的外键。当数据库构建查询时，它会连接另一个表两次。为了在 SQL 查询中进行说明（不确定 MySQL 语法，我很久没有使用它了，所以这是专门的 MS SQL 语法），您在选择数据时会为该表提供两个不同的别名。像这样的东西：
SELECT
student_accounts.name AS student_name,
counselor_accounts.name AS counselor_name
FROM
student_rec
INNER JOIN user_accounts AS student_accounts
ON student_rec.student_number = student_accounts.user_id
INNER JOIN user_accounts AS counselor_accounts
ON student_rec.guidance_counselor_id = counselor_accounts.user_id
This essentially takes the student_rectable and combines it with the user_accountstable twice, once on each column, and assigns two different aliases when combining them so as to tell them apart.
这实质上是将student_rec表与表组合user_accounts两次，每列一次，并在组合时分配两个不同的别名以区分它们。
回答by michel-slm
Yes, there should be no problem. Foreign keys and primary keys are orthogonal to each other, it's fine for a column or a set of columns to be both the primary key for that table (which requires them to be unique) and also to be associated with a primary key / unique constraint in another table.
是的，应该没有问题。外键和主键彼此正交，一列或一组列既可以作为该表的主键（这要求它们是唯一的）也可以与主键/唯一约束关联在另一张桌子上。
