Re: primary and foregin keys

From: <rallykarro_at_hotmail.com>
Date: 24 Dec 2004 01:13:29 -0800
Message-ID: <1103879609.454318.191970_at_z14g2000cwz.googlegroups.com>


Jonathan Leffler wrote:
> Alan wrote:
>
> > "karolina" <rallykarro_at_hotmail.com> wrote:
> >>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?
>
> Not necessarily - you can have a foreign key in a table which
> references its own primary key. If you look at Date's definitions,
> they tend to refer to 'two tables (or relvars), not necessarily
distinct'.
>
>
> Going back to the original question, though, the 'foreign key' is
> always satisfied - any given row contains a prod_number that appears
> in the (same row of the) same table. So, it is a pointless
> constraint. (And, if the FK condition meant that the current row was

> not a candidate for matching, then you'd always have to insert at
> least two rows with the given prod_number when you first inserted a
> prod_number, and the checking would have to be appropriately
deferred.)

Hmmm...
Why is the foreign key always satisfied? If there are products that are put in "storage" table which not is part of the "product" table I want the foregin key to constraint that behaviour. Why is that a pointless constraint?
I always want to make sure that the products put in "storage" is present as a valid product in the "product" table. If not, I want the foregin key constraint to fall out.

> --
> Jonathan Leffler #include <disclaimer.h>
> Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
> Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
Received on Fri Dec 24 2004 - 10:13:29 CET

Original text of this message