Re: Create a foreign key from a field that is part of a mulitple primary key

"Turkbear" <john.g_at_dot.spamfree.com> wrote in message news:hpiej0d4av0ucubjf95ud511sval66p049_at_4ax.com...
| "Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:
|
| >
| >"naud" <naudjf_at_yahoo.fr> wrote in message
| >news:1e1a7efc.0409020717.48e48228_at_posting.google.com...
| >| Hi,
| >|
| >| i had a table named T_A, with a double primary key : (A,B)
| >|
| >| Then i created a table named T_B with a field : C.
| >|
| >| I want this field T_B.C to take only values that already exist in
| >| field T_A.A.
| >|
| >| So i tried to create a foreign key on my field T_B.C, pointing on the
| >| field T_A.A : an error message appeared "not possible to create a
| >| foreign key on a field that is not a primary key".
| >|
| >| How can i solve this....? If U have any idea, please mail me !!
| >|
| >| THANX very much.
| >
| >
| >whenever you have a multi-part primary key, as in T_A(A,B), you have
either
| >a dependent table or an intersection table -- that implies that part of
the
| >PK is actually an FK to another table.
| >
| >so in your case, column A looks like it should be an FK to the PK of
another
| >table 'T_X' (which appears to be missing)
| >
| >once that table (T_X) is created with the appropriate PK (and TA(A) is
| >declared as an FK to it) then you can create an FK on T_B(C) referencing
the
| >correctly modelled PK of table 'T_X'
| >
| >++ mcs
| >
|
| I do not follow your
| "whenever you have a multi-part primary key, as in T_A(A,B), you have
either
| a dependent table or an intersection table -- that implies that part of
the
| PK is actually an FK to another table."
|
| We have multi-field ( compound) Primary Keys in several tables since the
only way to get a unique value for those tables is
| to use more than one field..It has nothing to do with dependent or
intersection tables...
|

your design is definitely missing tables -- which may be out of scope, but the fact that you require more than one column to get a unique value shows that there is another entity that contains is the 'master' record of those in the multi-column PK table. guaranteed.

give me any of you table layouts and i'll identify the missing entities/tables for you.

++ mcs Received on Thu Sep 02 2004 - 15:41:24 CDT