Re: no unique constraint matching given keys for referenced table

Lonni J Friedman <netllama(at)gmail(dot)com> writes:
> I'm trying to create some references while creating a few new tables
> and its going poorly. For each attempt, I'm getting the error:
> ERROR: there is no unique constraint matching given keys for
> referenced table "foo"

> Here's what table foo looks like:

> Column | Type |
> Modifiers
> ----------------+-----------------------------+--------------------------------------------------------
> id | integer | not null default
> nextval('foo_id_seq'::regclass)
> date_created | timestamp without time zone | not null
> Indexes:
> "foo_pkey" PRIMARY KEY, btree (id)

> Here's how I'm attempting to create the new table (bar):
> # create table bar (id serial PRIMARY KEY, suiteid integer REFERENCES foo(id)) ;
> NOTICE: CREATE TABLE will create implicit sequence "bar_id_seq" for
> serial column "bar.id"
> NOTICE: CREATE TABLE / PRIMARY KEY will create implicit index
> "bar_pkey" for table "bar"
> ERROR: there is no unique constraint matching given keys for
> referenced table "foo"

Huh, that certainly looks like it ought to work. The only idea that
comes to mind offhand is that it's finding the wrong "foo" table because
of a schema search path issue. If that's not it, what PG version is
this? Could we see the results of "pg_dump -s -t foo"?

regards, tom lane