title: Can a foreign key act as a primary key?
body: <p>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?</p>
  
  <p>Here are some of the tables of our system's database design:</p>
  
  <pre><code>user_accounts
  students
  guidance_counselors
  </code></pre>
  
  <p>What I wanted to happen is that the <code>user_accounts</code> 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 <code>students</code> and <code>guidance_counselors</code> table are also the foreign key from the <code>user_accounts</code> table. But I am not sure if it is allowed.</p>
  
  <p>Another question is: a <code>student_rec</code> table also exists, which requires a <code>student_number</code> (which is the <code>user_id</code> in the <code>user_accounts</code> table) and a <code>guidance_counsellor_id</code> (which is also the <code>user_id</code> in the <code>user_accounts</code>) for each of its record. If both the IDs of a student and guidance counselor come from the <code>user_accounts table</code>, how would I design the <code>student_rec</code> table? And for future reference, how do I manually write it as an SQL code?</p>
  
  <p>This has been bugging me and I can't find any specific or sure answer to my questions.</p>