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

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Thu, 19 Dec 2002 12:27:10 -0000
Message-ID: <3e01bb1e$0$246$ed9e5944@reading.news.pipex.net>

"Giovanni Azua" <bravegag_at_hotmail.com> wrote in message news:atsbn8$25ar5$1_at_ID-114658.news.dfncis.de...
> 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?

SQL> ed
Wrote file afiedt.buf

  1 create table purchase_product(
  2 Customer_ID number,
  3 Product_ID number,
  4 Branch_ID number,
  5 Purchase_Date date default sysdate,   6 color_id number,
  7 size_id number,
  8 description varchar2(1000))
  9* tablespace users
SQL> / Table created.

SQL> alter table purchase_product
2 add constraint pk_pp primary key
(customer_id,product_id,branch_id,purchase_date);

Table altered.

SQL> desc purchase_product;

 Name                                                              Null?

Type

--------
CUSTOMER_ID NOT NULL NUMBER PRODUCT_ID NOT NULL NUMBER BRANCH_ID NOT NULL NUMBER PURCHASE_DATE NOT NULL DATE COLOR_ID NUMBER SIZE_ID NUMBER DESCRIPTION VARCHAR2(1000)

SQL> alter table
2 purchase_product modify product_id null; purchase_product modify product_id null

ERROR at line 2:
ORA-01451: column to be modified to NULL cannot be modified to NULL

SQL>
> 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.

They could however only buy 1 of the same colour and size. Fortunately although purchase_date is a date column that is a datetime datatype so in fact the restriction doesn't apply to the same DAY but the same day and time (i.e Niall buys 3 large blue sweatshirts in one transaction).

I'm also a little confused as to why you 'prefer to use natural keys' but then have a bunch of id (or artificial key) columns in the table. I'd follow exactly what you seem to have done with the other tables and use a new column purchase_product_id as the primary key.

--
Niall Litchfield
Oracle DBA
Audit Commission UK
*****************************************
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer

******************************************

Received on Thu Dec 19 2002 - 06:27:10 CST