keeztian keeztian - 4 months ago 10
SQL Question

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


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

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.

Comments