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

Piliform

You can do this in Yii framework – Jacintojack

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, Volume 1: Avoiding the Pitfalls of Database Programming.

Dirigible

Answer 2

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.

Atonal

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).

Thorin

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.

Vivisectionist

MySQL has, but doesn't enforce, CHECK constraints. What you describe is still a foreign key relationship, just to the same table - which usually means needing recursive/hierachical queries, which MySQL doesn't have (PostgreSQL 8.x does). – Thorin

That was not what I wanted to do, I was not so clear about what I meant. Look at my updated tables and columns and you will understand what I am looking for. – Piliform

As much as i loathe this type of answer i definitely get the sense of "what you want is wrong" here. I think @Bill Karwin has the right idea. – Vivisectionist