Re: compound primary key with null columns... is it possible?

From: Brian E Dick <bdick_at_cox.net>
Date: Thu, 19 Dec 2002 14:14:29 GMT
Message-ID: <9vkM9.13401$pe.714981@news2.east.cox.net>

The typical pattern for implementing a purchase order is to use a parent/child relationship based on surrogate keys. Then you don't have problems like your describing. Natural keys are not robust if they break relational theory.

Something like the following.

create table purchase_order
(order_id number not null

,customer_id number
,branch_id number
,order_date date
,...
,primary key (order_id)

)

create table order_item
(order_id number not null

,item_number number not null
,product_id number
,color_id number
,size_id number
,...
,primary key(order_id, item_number)

)

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:atsbn8$25ar5$1_at_ID-114658.news.dfncis.de...
> Hello all,
>
> I have to refactor a production schema :-( and the
> "easiest way" is to have an existing compound primary
> key additionally include a couple more of columns
> (the primary key index is perhaps already too large
> (6 columns) but I always prefer natural keys rather than
> virtual keys, i.e. robustness vs performance) given that
> this entity is kind of "final" i.e. doesn't actually link to anyone
> else in this schema...
>
> The problem is that the two new columns could likely
> have null values and I was aware of the capability of having
> nullable columns inside unique indexes, is it possible with
> primary keys as a special case?
>
> My design is like this:
>
> Purchase_Product is an entity that describe a purchase (of
> course) and is uniquelly identified by:
>
> Customer_ID
> Product_ID
> Branch_ID
> Purchase_Date
>
> now I would like to add to this PK also COLOR_ID and SIZE_ID
> so the customers could also buy different of those the same day.
>
> TIA,
> Best Regards,
> Giovanni
>
>
Received on Thu Dec 19 2002 - 08:14:29 CST