Is a Foreign Key to Multiple Tables Possible in Relational Databases?

Foreign Key to One of Multiple Tables: Is It Possible?

In relational databases, foreign keys are used to establish relationships between tables. Typically, a foreign key in one table points to a primary key in another table. However, the question arises: can a foreign key refer to one of multiple tables?

The Answer: No

A foreign key constraint always references exactly one parent table. This means that it is not possible to define a foreign key that points to one of several possible tables. This behavior is the same in both MySQL and PostgreSQL.

Implications for Polymorphic Relationships

Polymorphic relationships are where a table may have a relationship with one of several other tables. In the example provided, the "images" table has a relationship with one of two tables: "subordinates" or "products."

In such cases, it is not possible to directly create a foreign key that points to either "subordinates" or "products." Instead, a join table must be explicitly defined to establish the relationship. The join table contains the foreign keys to both "subordinates" and "products," and the "images" table then has a foreign key referencing the join table.

Additional Resources

For further exploration of this topic, consider the following resources:

• Stack Overflow: Why can you not have a foreign key in a polymorphic association?
• Stack Overflow: Possible to do a MySQL foreign key to one of two possible tables?
• DBA Stack Exchange: Referencing foreign keys in the same column
• Michael Feathers: Practical Object-Oriented Models in SQL
• Bill Karwin: SQL Antipatterns, Volume 1: Avoiding the Pitfalls of Database Programming

The above is the detailed content of Is a Foreign Key to Multiple Tables Possible in Relational Databases?. For more information, please follow other related articles on the PHP Chinese website!