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:
Show quoted text
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, Pennsylvania 19026I 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:
Show quoted text
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.comOn 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, Pennsylvania 19026