Table with multiple foreign keys?

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
Sleepless
Forum Newbie
Posts: 1
Joined: Mon Sep 28, 2009 7:16 pm

Table with multiple foreign keys?

Post 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!
User avatar
Darhazer
DevNet Resident
Posts: 1011
Joined: Thu May 14, 2009 3:00 pm
Location: HellCity, Bulgaria

Re: Table with multiple foreign keys?

Post 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.
josh
DevNet Master
Posts: 4872
Joined: Wed Feb 11, 2004 3:23 pm
Location: Palm beach, Florida

Re: Table with multiple foreign keys?

Post 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
Post Reply