Re: no unique constraint matching given keys for referenced table

Lonni J Friedman <netllama@gmail.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