Re: primary and foregin keys

"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?

>
> 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 - 02:43:16 CET