mysql - Foreign key to one of many tables?

Question

The usual way of setting a foreign key constraint is to choose which table the foreign key will point to.

I'm having a polymorphic relation between 1 table and a set of table.

That means that this table will have a relation with one of those tables in the set.

eg.

images: person_id, person_type
subordinates: id, col1, col2...col9
products: id, colA, colB...colZ

In the above example, if person_type is "subordinates" then person_id should be a foreign key to subordinates.id and the same goes with products.

So I wonder, is it possible to have a foreign key to one of many tables, or do you have to specifically set which table it points to when you assign one.

This question is for both MySQL and PostgreSQL.

Thanks

Answer 1

No, a foreign key constraint always references exactly one parent table.

This question comes up frequently. Here are some of my past answers to it:

• Why can you not have a foreign key in a polymorphic association?
• Possible to do a MySQL foreign key to one of two possible tables?
• Referencing foreign keys in the same column
• In a StackOverflow clone, what relationship should a Comments table have to Questions and Answers?
• MySQL - Conditional Foreign Key Constraints
• How to handle an “OR” relationship in an ERD (table) design?
• MySQL: Two n:1 relations, but not both at once

For more on Polymorphic Associations, see my presentation Practical Object-Oriented Models in SQL or my book, SQL Antipatterns: Avoiding the Pitfalls of Database Programming.