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:sd2fj09ghb2da6lfj6q42ckcmmsc4cajpc_at_4ax.com...
| "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...
|

sorry if my assertion seemed like an affront

i don't know your data structures, but i know relational design -- and i still guarantee you that any tables you've got with multi-part PKs indicate a missing (or non-modeled, or out-of-scope) entity -- which may or may not ever be a problem. but in the OP's case, that's exactly what the problem was when he/she need to define an FK but only had a 'sibling' table, not the 'parent' table -- and i've seen that scenario an incredible number of times with some very unfortunate kludges attempting to relate the siblings

keep in mind, DBA skills, no matter how great they are, are not the same as Data Modeler skills -- not saying you don't have them, just that it's not the same science.

so, if you're offended, sorry. but if you're up for a lively discussion, why not post a structure that you feel requires more than one column for the PK -- either we'll see each other's side better, or you'll have the satisfaction of proving me wrong ;-)

++ mcs

(by the way, i've been a DBA, developer, data modeler, etc for 18+ years, and a pretty good one, but there's a whole lot of things i haven't figured out yet -- you never stop learning, right?) Received on Thu Sep 02 2004 - 16:21:17 CDT