I have four MySQL tables: A, B, C and Z.
Tables A, B and C each has a foreign key into a record in Z.
What is the best way to include foreign keys in Z to point back into A, B, and C? Naturally, each record in Z only belongs to one record (in either A, B, or C).
Thanks!
Table with multiple foreign keys?
Moderator: General Moderators
Re: Table with multiple foreign keys?
Why you want to create a back-reference?
Anyway, foreign key can be declared to allow null, which means that the field should have either a value that is present in the foreign table, or to be null - no reference.
Anyway, foreign key can be declared to allow null, which means that the field should have either a value that is present in the foreign table, or to be null - no reference.
Re: Table with multiple foreign keys?
Association table, you would have a "composite foreign key" ( dunno if thats a real term )
Basically in your assoc table you could have
relationship_table | relationship_id as your fields
relationships table would say wether it is a foreign key for A B or C
but you could just just do a select union all from a b c where id in z
Basically in your assoc table you could have
relationship_table | relationship_id as your fields
relationships table would say wether it is a foreign key for A B or C
but you could just just do a select union all from a b c where id in z