Re: primary and foregin keys
Date: Fri, 24 Dec 2004 04:37:58 GMT
Message-ID: <G2Nyd.8373$RH4.1640_at_newsread1.news.pas.earthlink.net>
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.)
-- 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 - 05:37:58 CET