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: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?)
>

Did not mean to sound affronted - just a little surprised that someone without knowledge of my situation can make a blanket assumption about what must be possible ( BTW, in addition to DBA duties I have also modeled - mainly using Oracle's designer)..
Our data is not suitable for single field uniqueness ( and we do not produce the data - it is from state-wide data warehouse view(s) ) since the only difference between records, in many cases , is in a combination of fields ( 5 fields in the index to make it unique - only one may change between records ) ..The underlying data structures we need to use are already denormalized for reporting purposes and are views based on several underlying tables from a TP system that we cannot access directly..So my constraints ( on my design, not on the data) are in the source of the data and it mandates that we use such a key structure.

This is not to imply that this is a good design, just that it is a necessary one given the circumstances...

Thanks for taking the time to post a serious reply...

John Received on Fri Sep 03 2004 - 10:45:59 CDT