Menu
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?
461
The FOREIGN KEY constraint is a key used to link two tables together.
A table can reference a maximum of 253 other tables and columns as foreign keys (outgoing references).
MySQL allows us to add a FOREIGN KEY constraint on multiple columns in a table. The condition is that each Foreign Key in the child table must refer to the different parent table.
If there is a one-to-many relationship between two entities, add the key of the entity on the “one” side (the parent) into the child table as a foreign key.
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:
I like the "supertable" approach in general. You may also want to check out this post for another example:
132
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
