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

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 - 05:47:36 CST