Can a Foreign Key Reference Multiple Primary Keys in Different Tables, and What's the Best Alternative?

Cross-table foreign key reference

This article explores the possibility of creating foreign keys in one table to reference the primary keys of multiple other tables. Specifically, it revolves around a scenario posed by the questioner.

Scene interpretation

The questioner has two tables, employees_ce and employees_sn, each with its own primary key column. They also have a third table called deductions that requires a foreign key to reference the primary keys of employees_ce and employees_sn. The example provided further illustrates this situation:

<code>employees_ce
--------------
empid   name
khce1   prince

employees_sn
----------------
empid   name
khsn1   princess</code>

<code>deductions
--------------
id      name
khce1   gold
khsn1   silver</code>

Solution

While the questioner's goal is technically achievable, it is not a best practice in database design. The recommended method is to define an intermediate table to link the two tables, for example:

<code>employees
----------------
empid   name

employees_types
---------------
empid   type
khce1   ce
khsn1   sn</code>

With this setup, the deductions table can have a foreign key referencing the employees column in the empid table, thus creating a simple relationship between the three tables.

<code>deductions
--------------
id      name
khce1   gold
khsn1   silver</code>

Advantages of the intermediate table method

The intermediate table method provides the following advantages:

• It simplifies data management by providing a single reference point to reference employees.
• It avoids the problem of foreign keys referencing multiple primary keys, which can lead to inconsistencies and data integrity issues.
• It allows flexibility and extensibility, with the ability to add other employee types without changing the deductions table.

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