Re: RE:REFERENCES

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 have announce id in table purchase as PRIMARY KEY. I do not know how to key another keywords.
> >>
> >> Best regards,
> >>
> >> Terence
> >> ==================================================================
> >> sSMSuTGhttp://sms.sina.com.hk
> >> WL
> >> 50MB qll Ghttp://sinamail.sina.com.hk
> >>
> >>
> >>
> >>
> >> ---------------------------(end of broadcast)---------------------------
> >> TIP 4: Don't 'kill -9' the postmaster
> >>
> >
> >
> ==================================================================
> sSMSuTGhttp://sms.sina.com.hk
> WL
> 50MB qll Ghttp://sinamail.sina.com.hk
>
>
>