Can a Single Foreign Key Reference Multiple Primary Keys in Different Tables?

Is it Possible for a Foreign Key to Reference Multiple Primary Keys Across Different Tables?

A frequent database design question involves referencing primary keys from multiple tables using a single foreign key column. Let's illustrate with an example:

Database: employee information

Tables:

• employees_ce (Primary Key: empid)
• employees_sn (Primary Key: empid)
• deductions (Foreign Key: id)

Can the id foreign key in the deductions table reference both empid primary keys in employees_ce and employees_sn?

The Solution:

Although it might seem intuitive to link a single foreign key to multiple primary keys across different tables, standard database design doesn't permit this. The best practice is to introduce a linking table to connect the related entities.

Improved Database Structure:

• employees (Primary Key: id)
• employees_ce (Foreign Key: id)
• employees_sn (Foreign Key: id)
• deductions (Foreign Key: employee_id)

This revised structure uses a single employees table with a primary key (id) to represent all employees, irrespective of their type. employees_ce and employees_sn now use foreign keys referencing the id column in employees, linking them to their respective employee categories. The deductions table's foreign key, employee_id, points to the primary key in the employees table. This approach ensures data integrity and simplifies queries and joins between tables.

The above is the detailed content of Can a Single Foreign Key Reference Multiple Primary Keys in Different Tables?. For more information, please follow other related articles on the PHP Chinese website!