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

From: Alan Shein <alan.remove_this.shein_at_erols.com>
Date: Thu, 19 Dec 2002 15:15:26 -0500
Message-ID: <att9cs$2eob5$1@ID-114862.news.dfncis.de>

All you have to do is try it...

SQL> CREATE TABLE test
  2 (
  3 cust_id char(3),
  4 product char(3),
  5 branch char(3),
  6 sale_date date,
  7 color char(3),
  8 size_nbr char(3)
  9 )
 10 ;

Table created.

  1* alter table test add primary key(cust_id, product, branch, sale_date, color, size_nbr)
SQL> / Table altered.

  1 insert into test values(

  2  1,
  3  1,
  4  1,

  5 '19-DEC-02',
  6 null,
  7 1
  8* )
  9 /
insert into test values(

            *
ERROR at line 1:
ORA-01400: mandatory (NOT NULL) column is missing or NULL during insert

Insert a value such as '000' or 'Unknown' instead of a null:

  1 insert into test values(

  2  1,
  3  1,
  4  1,

  5 '19-DEC-02',
  6 '000',
  7 1
  8* )
SQL> / 1 row created.

"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 - 14:15:26 CST