Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: compound primary key with null columns... is it possible?

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

From: Giovanni Azua <bravegag_at_hotmail.com>
Date: Thu, 19 Dec 2002 14:28:53 +0100
Message-ID: <atshl6$280oh$1@ID-114658.news.dfncis.de>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US