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

A foreign-key by definition must point to either a primary- or candidate- key on one-and-only-one table - primary- only is available in a typical DBMS. You're better off having a single 'person' table and have tables that are related to this with e.g. manager information.

Answer 2

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.

Answer 3

A column is only a placeholder for a value. A foreign key constraint means that the data stored within that column can only be a value that matches the table's column defined in the constraint. Foreign key constraints are per table...

There is nothing to stop you from defining multiple foreign key constraints on a column. But this means that the only value allowed to be stored will be values that already exist in all of the other foreign related tables. IE: TABLE_1 has values 1 and 2, TABLE_2 has values 2 and 3 - TABLE_3 has foreign key relationships defined to tables 1 & 2 on TABLE_3's col column... The only valid value I can insert into TABLE_3.col is 2, because it's in both tables (assuming col is not nullable).

Answer 4

A foreign key can only ever point to a single table.

It looks to me like what you really wanted to do here is create a parent id in your persons table. The subordinates would have a parent id pointing at their managers. If a subordinate needed to have multiple managers a separate joining table could be created with 2 columns each containing a person id one being the subordinate and the other being one of the managers.

If you wanted to restrict who could be assigned to the parentid field this could be done with a check constraint.