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

Giovanni Azua wrote:
> 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
>
>

Sounds like a cross-table to me. Incorporate all columns of the primary keys of the tables:

- branche
- customer
- product
- purchase item (which would have all PK columns of Purchase

   Order, plus something like PO line)

On the other hand, the design looks strange to me - your product does not have a correct unique id, or your requirement to add attributes like color (colour if you like) and size is wrong. Retrieve these from the PO line table.

And... oracle will NOT allow NULL columns in a primary key: 8.1.7.4 EE on Linux:

create table foo2 (
prod_id number not null,
po_id number not null,
color varchar2(10) null)

SQL> desc foo2

  Name                                      Null?    Type
  ----------------------------------------- -------- ----------
  PROD_ID                                   NOT NULL NUMBER
  PO_ID                                     NOT NULL NUMBER
  COLOR                                              VARCHAR2(10)

SQL> alter table foo2
      add constraint foo2_pk
      primary key(prod_id, po_id, color);

Table altered.

SQL> desc foo2

  Name                                      Null?    Type
  ----------------------------------------- --------
  PROD_ID                                   NOT NULL NUMBER
  PO_ID                                     NOT NULL NUMBER
  COLOR                                     NOT NULL VARCHAR2(10)

color changed from NULLable to NOT NULL.

Grtz, Frank Received on Thu Dec 19 2002 - 06:33:06 CST