外键可以作为主键吗？ [英] Can a foreign key act as a primary key?

问题描述

我目前正在为我们团队的项目设计一个数据库结构。我现在有这个问题：在另一个表上有一个外键可以作为主键？

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

我想要发生的是， user_accounts 表应该包含ID系统）和学生用户和指导顾问用户的密码。简而言之，学生和 guidance_counselors 表的主键也是 user_accounts 表。但是我不知道是否允许。

What I wanted to happen is that the user_accounts table 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 students and guidance_counselors table are also the foreign key from the user_accounts table. But I am not sure if it is allowed.

另一个问题是：a student_rec 表也存在， a student_number （这是 user_accounts 表中的 user_id ）和 guidance_counsellor_id （也是 user_accounts user_id c>）。如果学生和指导顾问的ID都来自 user_accounts表，我将如何设计 student_rec 表？为了将来参考，我如何手动写它作为一个SQL代码？

Another question is: a student_rec table also exists, which requires a student_number (which is the user_id in the user_accounts table) and a guidance_counsellor_id (which is also the user_id in 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_rec table? And for future reference, how do I manually write it as an SQL code?

这已经bug了我，我找不到任何具体或肯定的答案，我的问题。

This has been bugging me and I can't find any specific or sure answer to my questions.

推荐答案

当然。这是一种常见的技术，称为超类表。如在你的例子中，这个想法是一个表包含实体的超集，并且具有描述一般实体的公共属性，并且其他表包含具有特定属性的那些实体的子集。这与面向对象设计中的简单类层次结构不同。

Of course. This is a common technique known as supertyping tables. 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.

对于第二个问题，一个表可以有两列，分别是另一个表的外键。当数据库构建查询时，它将连接另一个表两次。为了说明一个SQL查询（不确定MySQL语法，我没有使用它在很长时间，所以这是特定的MS SQL语法），你会给选择数据时两个不同别名的表。像这样：

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:

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

这本质上需要 student_rec 它与 user_accounts 表两次，每列一次，并且在组合它们时分配两个不同的别名，以便区分它们。

This essentially takes the student_rec table and combines it with the user_accounts table twice, once on each column, and assigns two different aliases when combining them so as to tell them apart.

这篇关于外键可以作为主键吗？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！