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

From: Mark Bole <makbo_at_pacbell.net>
Date: Sun, 12 Sep 2004 20:20:58 GMT
Message-ID: <Kc21d.18749$i%3.7021@newssvr27.news.prodigy.com>

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).
>
>

I still can't understand what is deficient about Mark Stock's analysis of both the OP and Niall's example. Isn't there still a "missing" table which has MODEL alone for PK?

Paraphrased back to the terms of the OP, how do you create a table of, say, models that have generated unacceptably high rates of helpdesk calls over a period of time, and ensure that it only contains models that are in your current inventory?

--Mark Bole Received on Sun Sep 12 2004 - 15:20:58 CDT