0

Say if a real life operation like loaning a library book is to be performed on two entities Teacher and Student. And the operation details are present in a table like Loans or Transaction. How do I define a foreign key in the Loans table for both Teacher and Student which have different schemes for primary keys? (student has 6digit key but teacher has 4 digit)

A similar question arises when a hospital database system Appointment table attempts to link to both an Employee table and EmployeeRelatives table using a foreign key, where both Employees of the hospital and their relatives are to be given free treatment. How does one specify format of foreign key in Appointments table?

yolo
  • 2,757
  • 6
  • 36
  • 65

1 Answers1

1

A foreign key can reference only one table.

My solution in these cases is to introduce an additional table (People or whatever name you prefer) which contains all the IDs of Teacher and Student. Teacher and Student can also have the People table key as foreign key.

You can find other possible solutions here: Foreign Key to multiple tables

Community
  • 1
  • 1
mucio
  • 7,014
  • 1
  • 21
  • 33
  • This is good, as far as it goes. Taking it a step further: Teacher and Student tables can have a foreign key (PersonId) as the primary key. It's good enough for uniqueness purposes, and it keeps things simple. – Walter Mitty Feb 02 '15 at 17:59