Re: primary and foregin keys

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