Well I'd have ONE unique index (on model,serial#) and it would allow nulls.
This might not be a disaster.
--
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com
"Daniel Morgan" <damorgan_at_x.washington.edu> wrote in message
news:1094693883.50938_at_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 Thu Sep 09 2004 - 14:02:00 CDT
