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

Home -> Community -> Usenet -> comp.databases.theory -> Re: primary and foregin keys

Re: primary and foregin keys

From: <rallykarro_at_hotmail.com>
Date: 24 Dec 2004 02:39:26 -0800
Message-ID: <1103884766.063376.278630@f14g2000cwb.googlegroups.com>


Tony Andrews wrote:
> rallykarro_at_hotmail.com wrote:
> > As far as I understand the primary key shold be set on the columns
> > which make the rows in the table unique?!
> > Since I want history tracking of my products the valid post is
> actually
> > the one with the latest date. This is why the product_number is not
> > unique itself in my "product" table.
>
> True. But if you want history tracking per product then you need 2
> tables:
>
> create table product (prod_number integer constraint pk_prod primary
> key, ...);
>
> create table product_history (prod_number references product ,
> prod_date date,
> ..., constraint pk_prod_hist primary key (prod_number, prod_date));
> Now you storage table can have a foreign key to the product table.

Okey, I get your point. This solution will make me do a transfer of data from the "product" table into the "product_history" table each time the "product" table is updataded with an already existing "product_number". Let's imagine that I go with my solution. How would you solve the problem if you only got the tables "products" and

"storage". Would you use a foregin key pointing out the
"product_number" in "products" from "storage" even if the
"product_number" itself not is the primary key in "products" or is this
totaly against the constraint concept? Received on Fri Dec 24 2004 - 04:39:26 CST

Original text of this message

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