Postgres Pro
Downloads
Home   >   mailing lists

Thread: RE:REFERENCES

RE:REFERENCES

From
"ngterry"
Date:
I am sorry what I am trying is:

>CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
>
>CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));

replace with:
CREATE TABLE lcopen (lc numeric, po int REFERENCES purchase (po), PRIMARY KEY (lc));
but there is an error and I do not how to correct that.

>
>CREATE TABLE lcreceive (
>   id int,
>   lc numeric REFERENCES lcopen(lc),
>   purid int REFERENCES purchase(id)
>)
>;
>
>I think this is what you are asking this will effectively make lc and
>purid in lcreceive foreign keys of the other tables
>
>Darren Ferguson
>
>On Sun, 10 Mar 2002, [big5] ngterry wrote:
>
>>
>> Hi!
>>
>> I am just a newbie in postgresql.  What if:
>>
>> CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
>>
>> CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));
>>
>> CREATE TABLE lcreceive (id int, lc numeric REFERENCES lcopen, purid int REFERENCES purchase);
>>
>> How do I refer the keywords po in table lcopen to table purchase the po keywords?  I am quite confuse, since I have
announceid in table purchase as PRIMARY KEY.  I do not know how to key another keywords. 
>>
>> Best regards,
>>
>> Terence
>> ==================================================================
>> 新浪SMS短訊：http://sms.sina.com.hk
>> 給遠方親友傳上無限驚喜
>> 50MB 電子郵件 ：http://sinamail.sina.com.hk
>>
>>
>>
>>
>> ---------------------------(end of broadcast)---------------------------
>> TIP 4: Don't 'kill -9' the postmaster
>>
>
>
==================================================================
新浪SMS短訊：http://sms.sina.com.hk
給遠方親友傳上無限驚喜
50MB 電子郵件 ：http://sinamail.sina.com.hk

Re: RE:REFERENCES

From
Stephan Szabo
Date:
On Mon, 11 Mar 2002, [big5] ngterry wrote:

>
> I am sorry what I am trying is:
>
> >CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
> >
> >CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));
>
> replace with:
> CREATE TABLE lcopen (lc numeric, po int REFERENCES purchase (po), PRIMARY KEY (lc));
> but there is an error and I do not how to correct that.

The target of a references constraint must be defined to be unique via
either a unique constraint or primary key constraint.  Do you really want
two separate ids on purchase?

Re: RE:REFERENCES

From
Darren Ferguson
Date:
The reason it wont let you create the foreign key is because the field is
not unique aso the key would not know which row in the purchase table that
was refered too.

This below will work


CREATE TABLE purchase (

   id int,
   po int,
   date date,
    PRIMARY KEY(id),
    UNIQUE(po)
);

CREATE TABLE lcopen (
    lc numeric,
    po int REFERENCES purchase (po),
    PRIMARY KEY (lc)
);

Hope this helps

Darren
Darren Ferguson

On Mon, 11 Mar 2002, [big5] ngterry wrote:

>
> I am sorry what I am trying is:
>
> >CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
> >
> >CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));
>
> replace with:
> CREATE TABLE lcopen (lc numeric, po int REFERENCES purchase (po), PRIMARY KEY (lc));
> but there is an error and I do not how to correct that.
>
> >
> >CREATE TABLE lcreceive (
> >   id int,
> >   lc numeric REFERENCES lcopen(lc),
> >   purid int REFERENCES purchase(id)
> >)
> >;
> >
> >I think this is what you are asking this will effectively make lc and
> >purid in lcreceive foreign keys of the other tables
> >
> >Darren Ferguson
> >
> >On Sun, 10 Mar 2002, [big5] ngterry wrote:
> >
> >>
> >> Hi!
> >>
> >> I am just a newbie in postgresql.  What if:
> >>
> >> CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
> >>
> >> CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));
> >>
> >> CREATE TABLE lcreceive (id int, lc numeric REFERENCES lcopen, purid int REFERENCES purchase);
> >>
> >> How do I refer the keywords po in table lcopen to table purchase the po keywords?  I am quite confuse, since I
haveannounce id in table purchase as PRIMARY KEY.  I do not know how to key another keywords. 
> >>
> >> Best regards,
> >>
> >> Terence
> >> ==================================================================
> >> �s��SMS�u�T�Ghttp://sms.sina.com.hk
> >> �������ˤͶǤW�L������
> >> 50MB �q�l�l�� �Ghttp://sinamail.sina.com.hk
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 4: Don't 'kill -9' the postmaster
> >>
> >
> >
> ==================================================================
> �s��SMS�u�T�Ghttp://sms.sina.com.hk
> �������ˤͶǤW�L������
> 50MB �q�l�l�� �Ghttp://sinamail.sina.com.hk
>
>
>

Re: [?big5?]

From
Jan Wieck
Date:
=?big5?B?bmd0ZXJyeQ==?= wrote:
>
> I am sorry what I am trying is:
>
> >CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
> >
> >CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));
>
> replace with:
> CREATE TABLE lcopen (lc numeric, po int REFERENCES purchase (po), PRIMARY KEY (lc));
> but there is an error and I do not how to correct that.

    You  should get an error saying that "po" is missing a UNIQUE
    constraint.  So go ahead and make it unique.


Jan

>
> >
> >CREATE TABLE lcreceive (
> >   id int,
> >   lc numeric REFERENCES lcopen(lc),
> >   purid int REFERENCES purchase(id)
> >)
> >;
> >
> >I think this is what you are asking this will effectively make lc and
> >purid in lcreceive foreign keys of the other tables
> >
> >Darren Ferguson
> >
> >On Sun, 10 Mar 2002, [big5] ngterry wrote:
> >
> >>
> >> Hi!
> >>
> >> I am just a newbie in postgresql.  What if:
> >>
> >> CREATE TABLE purchase (id int, po int, date date, PRIMARY KEY (id));
> >>
> >> CREATE TABLE lcopen (lc numeric, po int, PRIMARY KEY (lc));
> >>
> >> CREATE TABLE lcreceive (id int, lc numeric REFERENCES lcopen, purid int REFERENCES purchase);
> >>
> >> How do I refer the keywords po in table lcopen to table purchase the po keywords?  I am quite confuse, since I
haveannounce id in table purchase as PRIMARY KEY.  I do not know how to key another keywords. 
> >>
> >> Best regards,
> >>
> >> Terence
> >> ==================================================================
> >> ·s®öSMSµu°T¡Ghttp://sms.sina.com.hk
> >> µ¹»·¤è¿Ë¤Í¶Ç¤WµL­­Åå³ß
> >> 50MB ¹q¤l¶l¥ó ¡Ghttp://sinamail.sina.com.hk
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 4: Don't 'kill -9' the postmaster
> >>
> >
> >
> ==================================================================
> ·s®öSMSµu°T¡Ghttp://sms.sina.com.hk
> µ¹»·¤è¿Ë¤Í¶Ç¤WµL­­Åå³ß
> 50MB ¹q¤l¶l¥ó ¡Ghttp://sinamail.sina.com.hk
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)
>


--

#======================================================================#
# It's easier to get forgiveness for being wrong than for being right. #
# Let's break this rule - forgive me.                                  #
#================================================== JanWieck@Yahoo.com #



_________________________________________________________
Do You Yahoo!?
Get your free @yahoo.com address at http://mail.yahoo.com