Thread: fkey + primary key does not work in current
It seems that foreign key does not work in current, if specified with primary key definition. Take a look at following example(works in 7.0.2.): test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' CREATE test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, primary key (ftest1,ftest2,ftest3,ftest4), CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) ERROR: columns referenced in foreign key constraint not found. However, if primary key definition is not used with fkey, it works. test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, CONSTRAINT constrname3 FOREIGN KEY(ftest1,ftest2, ftest3) REFERENCES PKTABLE); NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) CREATE Any thoughts? -- Tatsuo Ishii
On Fri, 15 Sep 2000, Tatsuo Ishii wrote: > It seems that foreign key does not work in current, if specified with > primary key definition. Take a look at following example(works in > 7.0.2.): > > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' > CREATE > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, primary key (ftest1,ftest2,ftest3,ftest4), CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > ERROR: columns referenced in foreign key constraint not found. Hmm, that's very strange. I wonder which columns it think didn't exist. It shouldn't be checking the pktable in that case, which would imply it doesn't believe the existance of ftest1,ftest2,ftest3. Probably a stupid mistake on my part. As soon as I clear off space to compile current, I'll look.
Hello Stephan, I think this listserver hates me. none of my messages seem to go through. Anyone read this ?? I need to get access to records that is marked expired in the database any idea how or if it's possible ? Please respond even if it's just to say that you don't know so I KNOW that my messages get through!!! Best regards,Eje mailto:MacAhan@fament.com The Family Entertainment Network http://www.fament.com Phone : 316-231-7777 Fax : 316-231-4066- Your Internet Solution Provider & PC Computer Solutions Provider-
Your message got though. I don't know the answer to your question, but I'll bet that it is NO. On Fri, 15 Sep 2000, Eje Gustafsson wrote: > Hello Stephan, > > I think this listserver hates me. none of my messages seem to go through. > Anyone read this ?? > > I need to get access to records that is marked expired in the database any > idea how or if it's possible ? > > Please respond even if it's just to say that you don't know so I KNOW that > my messages get through!!! > > > Best regards, > Eje mailto:MacAhan@fament.com > The Family Entertainment Network http://www.fament.com > Phone : 316-231-7777 Fax : 316-231-4066 > - Your Internet Solution Provider & PC Computer Solutions Provider - > > >
Has this been resolved? > > On Fri, 15 Sep 2000, Tatsuo Ishii wrote: > > > It seems that foreign key does not work in current, if specified with > > primary key definition. Take a look at following example(works in > > 7.0.2.): > > > > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3) ); > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' > > CREATE > > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, primary key (ftest1,ftest2,ftest3,ftest4), CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' > > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > > ERROR: columns referenced in foreign key constraint not found. > > Hmm, that's very strange. I wonder which columns it think didn't exist. > It shouldn't be checking the pktable in that case, which would imply > it doesn't believe the existance of ftest1,ftest2,ftest3. Probably > a stupid mistake on my part. As soon as I clear off space to compile > current, I'll look. > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026
I believe that I sent a patch on Sep 17 for this to -patches although I don't know if anyone saw it (it's in the archives, so I know it went through). Stephan Szabo sszabo@bigpanda.com On Mon, 16 Oct 2000, Bruce Momjian wrote: > Has this been resolved? > > > On Fri, 15 Sep 2000, Tatsuo Ishii wrote: > > > > > It seems that foreign key does not work in current, if specified with > > > primary key definition. Take a look at following example(works in > > > 7.0.2.): > > > > > > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3)); > > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' > > > CREATE > > > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, primary key (ftest1,ftest2,ftest3,ftest4), CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); > > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' > > > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > > > ERROR: columns referenced in foreign key constraint not found. > > > > Hmm, that's very strange. I wonder which columns it think didn't exist. > > It shouldn't be checking the pktable in that case, which would imply > > it doesn't believe the existance of ftest1,ftest2,ftest3. Probably > > a stupid mistake on my part. As soon as I clear off space to compile > > current, I'll look.
That's strange. I didn't see it. Can you send it over. The archives don't seem to be working again. > > I believe that I sent a patch on Sep 17 for this to -patches although > I don't know if anyone saw it (it's in the archives, so I know it > went through). > > Stephan Szabo > sszabo@bigpanda.com > > On Mon, 16 Oct 2000, Bruce Momjian wrote: > > > Has this been resolved? > > > > > On Fri, 15 Sep 2000, Tatsuo Ishii wrote: > > > > > > > It seems that foreign key does not work in current, if specified with > > > > primary key definition. Take a look at following example(works in > > > > 7.0.2.): > > > > > > > > test=# CREATE TABLE PKTABLE ( ptest1 int, ptest2 int, ptest3 int, ptest4 text, PRIMARY KEY(ptest1, ptest2, ptest3)); > > > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'pktable_pkey' for table 'pktable' > > > > CREATE > > > > test=# CREATE TABLE FKTABLE ( ftest1 int, ftest2 int, ftest3 int, ftest4 int, primary key (ftest1,ftest2,ftest3,ftest4), CONSTRAINT constrname3 FOREIGN KEY(ftest1, ftest2, ftest3) REFERENCES PKTABLE); > > > > NOTICE: CREATE TABLE/PRIMARY KEY will create implicit index 'fktable_pkey' for table 'fktable' > > > > NOTICE: CREATE TABLE will create implicit trigger(s) for FOREIGN KEY check(s) > > > > ERROR: columns referenced in foreign key constraint not found. > > > > > > Hmm, that's very strange. I wonder which columns it think didn't exist. > > > It shouldn't be checking the pktable in that case, which would imply > > > it doesn't believe the existance of ftest1,ftest2,ftest3. Probably > > > a stupid mistake on my part. As soon as I clear off space to compile > > > current, I'll look. > > -- Bruce Momjian | http://candle.pha.pa.us pgman@candle.pha.pa.us | (610) 853-3000+ If your life is a hard drive, | 830 Blythe Avenue + Christ can be your backup. | Drexel Hill, Pennsylvania19026