Re: primary and foregin keys

Alan wrote:
> "karolina" <rallykarro_at_hotmail.com> wrote in message
> news:c8a98246.0412231454.79950443_at_posting.google.com...
> > Hi,
> >
> > I have two questions regarding foregin keys.
> >
> > 1. I have two tables in my database. Table number one is called
> > "products" and are containing some columns including "prod_number"
and
> > "date". I want my primary key to be set at the columns like:
> > "ALTER TABLE PRODUCTS add CONSTRAINT PK_PROD PRIMARY KEY
> > (prod_number,date);"
> >
> > Now, I have table number two "storage" which contains two columns
> > "prod_number" and "remain". I want a foregin key to point into the
> > "product" table like:
> > "ALTER TABLE STORAGE add CONSTRAINT FK_STORE FOREIGN KEY
(prod_number)
> > REFERENCES PRODUCTS(prod_number);"
> >
> > This is because I can't have products in store which do not exists.
> > My first question is if it is okay to have a forgin key into
> > "products" wich does not point out the whole primary key(e.g
> > prod_number and date). If not, how do I solve the example above?
>
> By definition, a Foreign key is a Primary Key in another table. Why
do you
> need to include "date" as part of the PK?
>

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.

> >
> > 2. The "storage" table is empty from the begining. I loaded alot of
> > products into the database but have not any of them in storage. It
> > seems impossible to add the foregin key since oracle demands that
> > there must be data in the "storage" table in order to add the
foregin
> > key. How do I solve this problem?
> > I still want to define the constraint even if I don't have any data
in
> > my "storage" table yet!
> >
> > I am running oracle 10g
> >
> > thanks,
> >
> > Karolina
>
> Ask this question in comp.databases.oracle.misc
Received on Fri Dec 24 2004 - 09:59:50 CET