Обсуждение: ERROR: UNIQUE constraint ... not found?
Well, I've stared at this little riddle for long enough. Anyone see a
mistake?
$ cat test.sql
SELECT version();
CREATE TABLE person (
id SERIAL,
name VARCHAR NOT NULL
);
CREATE TABLE session (
id SERIAL,
person_id INTEGER NOT NULL,
FOREIGN KEY (person_id) REFERENCES person (id)
);
$ dropdb drilldb; createdb drilldb; psql -e -d drilldb -f test.sql
DROP DATABASE
CREATE DATABASE
SELECT version();
version
---------------------------------------------------------------------------------------------------------
PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2
20020903 (Red Hat Linux 8.0 3.2-7)
(1 row)
CREATE TABLE person (
id SERIAL,
name VARCHAR NOT NULL
);
psql:test.sql:6: NOTICE: CREATE TABLE will create implicit sequence
'person_id_seq' for SERIAL column 'person.id'
CREATE TABLE
CREATE TABLE session (
id SERIAL,
person_id INTEGER NOT NULL,
FOREIGN KEY (person_id) REFERENCES person (id)
);
psql:test.sql:12: NOTICE: CREATE TABLE will create implicit sequence
'session_id_seq' for SERIAL column 'session.id'
psql:test.sql:12: NOTICE: CREATE TABLE will create implicit trigger(s) for
FOREIGN KEY check(s)
psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for
referenced table "person" not found
On Wednesday April 2 2003 3:31, Ed L. wrote: > Well, I've stared at this little riddle for long enough. Anyone see a > mistake? > > CREATE TABLE person ( > id SERIAL, > name VARCHAR NOT NULL > ); > > CREATE TABLE session ( > id SERIAL, > person_id INTEGER NOT NULL, > FOREIGN KEY (person_id) REFERENCES person (id) > ); > > psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for > referenced table "person" not found Ah. I was wondering why this didn't work anymore when it worked in 7.1. I guess the change in SERIAL semantics where the unique index is no longer created is what causes the problem. If I used PRIMARY KEY or create a unique index manually on person.id, it works fine. It seems odd that one cannot have a foreign key to a non-indexed column in another table. Ed
If you create tables with sequences more than once, you have to drop the sequence before you recreate the table. Ed L. wrote: > Well, I've stared at this little riddle for long enough. Anyone see a > mistake? > > $ cat test.sql > SELECT version(); > > CREATE TABLE person ( > id SERIAL, > name VARCHAR NOT NULL > ); > > CREATE TABLE session ( > id SERIAL, > person_id INTEGER NOT NULL, > FOREIGN KEY (person_id) REFERENCES person (id) > ); > > > > $ dropdb drilldb; createdb drilldb; psql -e -d drilldb -f test.sql > DROP DATABASE > CREATE DATABASE > SELECT version(); > version > --------------------------------------------------------------------------------------------------------- > PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 > 20020903 (Red Hat Linux 8.0 3.2-7) > (1 row) > > CREATE TABLE person ( > id SERIAL, > name VARCHAR NOT NULL > ); > psql:test.sql:6: NOTICE: CREATE TABLE will create implicit sequence > 'person_id_seq' for SERIAL column 'person.id' > CREATE TABLE > CREATE TABLE session ( > id SERIAL, > person_id INTEGER NOT NULL, > FOREIGN KEY (person_id) REFERENCES person (id) > ); > psql:test.sql:12: NOTICE: CREATE TABLE will create implicit sequence > 'session_id_seq' for SERIAL column 'session.id' > psql:test.sql:12: NOTICE: CREATE TABLE will create implicit trigger(s) for > FOREIGN KEY check(s) > psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for > referenced table "person" not found > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) >
Serial no longer sets a unique index on the field, which I think it did prior to 7.3. You need to explicitly add one, as primary key I'd guess. On Wed, 2 Apr 2003, Ed L. wrote: > Well, I've stared at this little riddle for long enough. Anyone see a > mistake? > > $ cat test.sql > SELECT version(); > > CREATE TABLE person ( > id SERIAL, > name VARCHAR NOT NULL > ); > > CREATE TABLE session ( > id SERIAL, > person_id INTEGER NOT NULL, > FOREIGN KEY (person_id) REFERENCES person (id) > ); > > > > $ dropdb drilldb; createdb drilldb; psql -e -d drilldb -f test.sql > DROP DATABASE > CREATE DATABASE > SELECT version(); > version > --------------------------------------------------------------------------------------------------------- > PostgreSQL 7.3.2 on i686-pc-linux-gnu, compiled by GCC gcc (GCC) 3.2 > 20020903 (Red Hat Linux 8.0 3.2-7) > (1 row) > > CREATE TABLE person ( > id SERIAL, > name VARCHAR NOT NULL > ); > psql:test.sql:6: NOTICE: CREATE TABLE will create implicit sequence > 'person_id_seq' for SERIAL column 'person.id' > CREATE TABLE > CREATE TABLE session ( > id SERIAL, > person_id INTEGER NOT NULL, > FOREIGN KEY (person_id) REFERENCES person (id) > ); > psql:test.sql:12: NOTICE: CREATE TABLE will create implicit sequence > 'session_id_seq' for SERIAL column 'session.id' > psql:test.sql:12: NOTICE: CREATE TABLE will create implicit trigger(s) for > FOREIGN KEY check(s) > psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for > referenced table "person" not found > > > ---------------------------(end of broadcast)--------------------------- > TIP 2: you can get off all lists at once with the unregister command > (send "unregister YourEmailAddressHere" to majordomo@postgresql.org) > -- Nigel J. Andrews Telephone: +44 (0) 208 941 1136
On Wed, 2 Apr 2003, Ed L. wrote: > On Wednesday April 2 2003 3:31, Ed L. wrote: > > Well, I've stared at this little riddle for long enough. Anyone see a > > mistake? > > > > CREATE TABLE person ( > > id SERIAL, > > name VARCHAR NOT NULL > > ); > > > > CREATE TABLE session ( > > id SERIAL, > > person_id INTEGER NOT NULL, > > FOREIGN KEY (person_id) REFERENCES person (id) > > ); > > > > psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for > > referenced table "person" not found > > Ah. I was wondering why this didn't work anymore when it worked in 7.1. I > guess the change in SERIAL semantics where the unique index is no longer > created is what causes the problem. If I used PRIMARY KEY or create a > unique index manually on person.id, it works fine. It seems odd that one > cannot have a foreign key to a non-indexed column in another table. SQL requires that the target column list match the target column list of a unique constraint on the target table. If the target wasn't unique everything'd end up having to be defined similarly to the way match partial is (dealing with matching rows and unique matching rows).
On Wed, 2 Apr 2003, Ed L. wrote: > > psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for > > referenced table "person" not found > > Ah. I was wondering why this didn't work anymore when it worked in 7.1. I > guess the change in SERIAL semantics where the unique index is no longer > created is what causes the problem. If I used PRIMARY KEY or create a > unique index manually on person.id, it works fine. It seems odd that one > cannot have a foreign key to a non-indexed column in another table. > Well, I suppose a trigger on insert and update could be used to impose the unique constraint, which is what the foriegn key needs, but then every check and use wouldn't have an index it could potentially use for the lookup. -- Nigel J. Andrews
Which record in the parent table would the foreign key refer to if there were multiple (non unique) records with the same value in the referenced table? Ed L. wrote: > On Wednesday April 2 2003 3:31, Ed L. wrote: > >>Well, I've stared at this little riddle for long enough. Anyone see a >>mistake? >> >>CREATE TABLE person ( >> id SERIAL, >> name VARCHAR NOT NULL >>); >> >>CREATE TABLE session ( >> id SERIAL, >> person_id INTEGER NOT NULL, >> FOREIGN KEY (person_id) REFERENCES person (id) >>); >> >>psql:test.sql:12: ERROR: UNIQUE constraint matching given keys for >>referenced table "person" not found > > > Ah. I was wondering why this didn't work anymore when it worked in 7.1. I > guess the change in SERIAL semantics where the unique index is no longer > created is what causes the problem. If I used PRIMARY KEY or create a > unique index manually on person.id, it works fine. It seems odd that one > cannot have a foreign key to a non-indexed column in another table. > > Ed > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Don't 'kill -9' the postmaster >