Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.misc -> Re: Create a foreign key from a field that is part of a mulitple primary key

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

From: Daniel Morgan <damorgan_at_x.washington.edu>
Date: Wed, 08 Sep 2004 18:37:00 -0700
Message-ID: <1094693883.50938@yasure>


Niall Litchfield wrote:

> Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1094424340.174831_at_yasure>...
> 

>>Niall Litchfield wrote:
>>
>>>"Mark C. Stock" <mcstockX_at_Xenquery .com> wrote in message
>>>news:SbSdnR0ccbBME6rcRVn-sQ_at_comcast.com...
>>>
>>>
>>>>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 ;-)
>>>
>>>
>>>A possibility for you. We have an helpdesk app. It contains, not entirely
>>>surprisingly, an inventory table. That table contains the fields MODEL and
>>>SERIAL#, we use (MODEL,SERIAL#) as the PK. I'm not prepared to guarantee
>>>uniqueness of the serial# across manufacturers and over time. I am prepared
>>>to gurantee the uniqueness of SERIAL# across the same model from the same
>>>manufacturer over time. I'll grant you that we could use an artificial key
>>>(and in fact it would be my preference), never the less for the design of
>>>the app the important thing is that it is an item of inventory (not that it
>>>is a laptop made by manufacturer y or a router made by company Z).
>>
>>You do understand, I presume, that if it was done your way you could
>>easily get duplicate model, serial#s into your table or be forced to
>>create a unique constraint (with its index) do duplicate exactly what
>>it is that your PK does for you now.
> 
> 
> 
> Daniel, are you replying to me or Mark here? If me I don't quite
> understand your point.
> 
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com

IIRC you. You, I believe, made the comment you'd rather have a surrogate key. My intention was to point out that it would only make things worse as you'd have to unique indexes, rather than one, to maintain.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace 'x' with 'u' to respond)
Received on Wed Sep 08 2004 - 20:37:00 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US