Can a foreign key act as a primary key?

database, foreign-keys, mysql, primary-key, sql

Solution

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.

Problem

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.

Original source