Foreign key constraint that points to one of several tables

Question

I have a table with one column source_id whose value should be the primary key of another table, though which table it is will vary from record to record. Every record must have a value for source_table that specifies the table for the source record, and a value for source_id that specifies the row in the source table.

Is there any way to accomplish this to take advantage of the DB's foreign key constraints and validation? Or will I have to move my validation logic into the application layer? Alternately, is there another design that will just let me avoid this problem?

Answer 1

Foreign key constraints can only reference one target table. "Conditional" foreign keys which reference a different target table based on some other field are not available in SQL. As @OMG Ponies noted in a comment below, you can have more than one foreign key on the same column, referencing more than one table, but that would mean the value of that column will have to exist in all the referenced tables. I guess this is not what you are after.

For a few possible solutions, I suggest checking out @Bill Karwin's answer to this question:

• Possible to do a MySQL foreign key to one of two possible tables?

I like the "supertable" approach in general. You may also want to check out this post for another example:

• MySQL - Conditional Foreign Key Constraints

Answer 2

I think previous answers do answer the first part of the question well. However link recommended by Daniel provides a solution only for the case when the number of referenced "source" tables is reasonably small. And the solution will not scale easily if you decide to increase the number of "source" tables.

To recommend a better strategy it would be nice to have a little more details on what the task is and if the "source" tables have anything in common that would allow to combine them.

In current structure (as far as I can infer from the question) I would reverse the relationship:

1. I would create a table (let's call it AllSources) that would work as a repository of all available sources with columns source_id and source_table. Both included in the primary key.
2. I would create foreign keys from each "source" table referencing AllSources table so that they could have only sources already registered in it.
3. Then I would create the table you mentioned in your question with foreign key referencing the AllSources table (not separate "source" tables).

Drawback: you will have to manage AllSources and "source" tables together ensuring that if you create a record in AllSources, you also create a corresponding record in proper "source" table, which in reality is not that hard.