Re: ERROR: UNIQUE constraint ... not found?

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)
>