A foreign key to one of many tables?

Question

The usual way to set a foreign key constraint is to select the table that the foreign key will point to.

I have a polymorphic relationship between 1 table and a table set.

This means that this table will be related to one of these 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 "subordinate", then person_id must be the foreign key for subordinates.id, and the same goes for products.

So, I wonder if it is possible to have a foreign key for one of the many tables, or you need to specifically indicate which table it points to when you assign it.

This question is for both MySQL and PostgreSQL.

thank

Answer 1

No, a foreign key constraint always refers to a single parent table.

This question often arises. Here are some of my past answers to it:

• Why don't you have a foreign key in a polymorphic association?
• Is it possible to use a MySQL foreign key for one of two possible tables?
• Foreign key reference in the same column
• stack overflow
• MySQL - Conditional External Key Constraints
• How to handle OR relationships in an ERD project (table)?
• MySQL: two n: 1 relationships, but not both at once

For more information about polymorphic associations, see my presentation Practical Object Oriented Models in SQL or in my book SQL Antipatterns: Troubleshooting Database Programming Errors .

Answer 2

By definition, a foreign key must indicate either a primary key or a candidate key in the table, and only one - primary access is available only in a typical DBMS. You better have one table "person" and have tables related to this, for example. management information.

Answer 3

The column is only a placeholder for the value. A foreign key constraint means that the data stored in this column can only be a value that matches the table column defined in the constraint. Foreign key constraints for a table ...

There is nothing to prevent you from defining several foreign key constraints for a column. But this means that the only value that can be saved will be the value that already exists in all other external related tables. IE: TABLE_1 has values ​​1 and 2, TABLE_2 has values ​​2 and 3 - TABLE_3 has foreign key relationships defined for tables 1 and 2 in column TABLE_3 col ... The only valid value that I can insert in TABLE_3.col is 2 because it is in both tables (assuming col not NULL).

Answer 4

A foreign key can point to only one table.

It seems to me that you really wanted to create a parent id in the table of your people here. Subordinates will have a parent identifier pointing to their managers. If the subordinate needs to have several managers, you can create a separate join table with two columns, each of which contains the identifier of the person who is the subordinate, and the other one of the managers.

If you want to restrict who can be assigned to the parentid field, you can do this with a check constraint.