----- Original Message ----- 
From: "Shravan Durvasula" <[EMAIL PROTECTED]>
To: "MySQL HELP" <[EMAIL PROTECTED]>
Sent: Tuesday, November 11, 2003 12:48 PM
Subject: Foreign Key


> Hi all:
>
> I have a table A(Id, Type). Primary Key is "Id"
> I have another table B(Id, State). Primary Key is "Id"
>
> I also have another table C(Id, ConditionId). Primary Key is "Id". But,
"ConditionId" could be "Id" values from Table A or Table B. So i want to
make it a foreign key. But how can i make the same attribute a foreign key
for more than one table?

If you really want to follow the canons of relational database theory, you
have to be very careful when you do this.  You can read up on this stuff in
discussions of the EER model (*enhanced* entity-relationship model).  I
think it's called a "category".

The way I've done it is to make another table, with just an Id column; call
it AB.  Each Id in A appears exactly once in AB, as does each Id in B.  (So
Id in A is both a pk for A and an fk pointing from A to AB; similarly for B.
And of course Id in AB is the pk for AB.)  Then ConditionId in C is a fk
pointing at AB, not at A or B.

> Thanks,
> -skd
>
>
> ---------------------------------
> Do you Yahoo!?
> Protect your identity with Yahoo! Mail AddressGuard


-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/[EMAIL PROTECTED]

Reply via email to