Re: ERROR: UNIQUE constraint ... 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