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

"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote:

>
>"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
>

Actually, since you do not know my data structures, you willingness to 'help' seems a little arrogant..

The data tables I was referring to do not have a single field that would make a record unique, nor is there some magical 'master' table that would..

I have been a DBA for over 15 years , and would have figured it out if it had one.

Thanks anyway... Received on Thu Sep 02 2004 - 16:03:29 CDT