Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: compound primary key with null columns... is it possible?
Oracle 9i (v9.2.02 W2K & Solaris SPARC).
Hello Niall,
Thank you for your helpful feedback ;-)
> 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.
>
The problem is that this DB was not thoughtfully
designed from scratch conceived in Oracle but is the
result of a very complex importing process from the
original MS Access the customer had, plenty of data
and that they wanted to keep as immutable as possible,
that is why there are artificial keys mixed up with natural
keys. Imagine they don't even have a catalog of descriptions
for their products but only a list of artificial codes, so my
application have to deal with their codes (already artificial keys).
> > 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).
>
Yes I know but this would fake somehow the initial idea of
robustness/consistency, since the users would then be aloud
to produce different entries for same tuples of:
customer_ID,product_ID,Branch_ID,Date,Color_ID,Size_ID
(for different date-times)
when should be suffice to only augment the quantities in the specific date (date only and no time).
Now that I know I can not have nullable columns as part of a primary key index my immediate solution is to provide logic null values e.g. -1 that can find a valid matching key on the master table e.g. Purchase_Product -> Color on Color_ID. This would be the best solution (faster update) for the currently running application (PL/SQL and Java Web application waiting to be updated ;-( has a big impact in the workaround).
Thank you again,
Best Regards,
Giovanni
Received on Thu Dec 19 2002 - 07:28:53 CST