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

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