mysql - Can a foreign key act as a primary key?

Question

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

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?

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

Answer 1

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.

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

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.