Foreign Key References Across Multiple Tables: A Structured Approach
This article addresses the challenge of creating a foreign key in a deductions table that references primary keys from both employees_ce and employees_sn tables within an "employees" database. Both employees_ce and employees_sn have unique primary keys. Is a direct dual foreign key reference possible?
The Efficient Solution: A Hierarchical Design
The optimal solution involves restructuring the database to reflect a hierarchical employee structure:
Employee Hierarchy: View employees as a parent class with employees_ce and employees_sn as subclasses.
Database Table Design: Create a three-table structure:
id
is the primary key.id
is a foreign key referencing employees.id
.id
is a foreign key referencing employees.id
.Foreign Key Relationships: Establish foreign key constraints:
employees_ce.id
references employees.id
employees_sn.id
references employees.id
Deductions Table: The deductions
table will now have a single foreign key, employee_id
, referencing the employees.id
column.
This hierarchical model ensures referential integrity and efficiently manages data for different employee types. It avoids the complexities and potential inconsistencies of attempting a direct dual foreign key reference. The single foreign key in the deductions
table provides a clear and unambiguous link to the relevant employee record.
The above is the detailed content of Can a Foreign Key Reference Primary Keys from Two Separate Tables Simultaneously?. For more information, please follow other related articles on the PHP Chinese website!