Page 1 of 1

Table with multiple foreign keys?

Posted: Mon Sep 28, 2009 7:26 pm
by Sleepless
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!

Re: Table with multiple foreign keys?

Posted: Sat Oct 03, 2009 8:44 am
by Darhazer
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.

Re: Table with multiple foreign keys?

Posted: Sat Oct 03, 2009 12:12 pm
by josh
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