Re: no unique constraint matching given keys for referenced table

On Tue, Aug 3, 2010 at 2:35 PM, Tom Lane <tgl@sss.pgh.pa.us> wrote:
> 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"?

Actually, I'm an idiot, and I was trying to be too smart when I wrote
the email.  The real problem was that I was attempting to create more
than 1 reference (there were more than just two columns in bar), and
the 2nd reference was to a column in foo that was not a primary key,
and also did not have a unique constraint).  Sorry for wasting your
time.  Next time I won't try to be clever when describing the problem.