Can a Foreign Key Reference Primary Keys from Two Separate Tables Simultaneously?

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:

1. Employee Hierarchy: View employees as a parent class with employees_ce and employees_sn as subclasses.

2. Database Table Design: Create a three-table structure:

   • employees (id, name, ...): A central table holding common employee attributes. id is the primary key.
   • employees_ce (id, ce_specific_data, ...): Stores data specific to employees_ce. id is a foreign key referencing employees.id.
   • employees_sn (id, sn_specific_data, ...): Stores data specific to employees_sn. id is a foreign key referencing employees.id.

3. Foreign Key Relationships: Establish foreign key constraints:

   • employees_ce.id references employees.id
   • employees_sn.id references employees.id

4. 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!