Re: primary and foregin keys
Date: 24 Dec 2004 02:39:26 -0800
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
> > 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
> 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 - 11:39:26 CET