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 00:59:50 -0800
Message-ID: <1103878790.622029.261580@c13g2000cwb.googlegroups.com>


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 - 02:59:50 CST

Original text of this message

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