Thread: no unique constraint matching given keys for referenced table

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"

My understanding is that this should work as long as foo.id is unique
(which it is).  I thought that maybe the lack of an explicit primary
key on foo might have been the problem, but I can't create one because
it already exists.  Clearly I'm missing something else fundamental?

thanks

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

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.

it is because one of your foreign keys is not set as primary key or unique in
referenced table.



--
View this message in context:
http://postgresql.1045698.n5.nabble.com/no-unique-constraint-matching-given-keys-for-referenced-table-tp2263411p5771066.html
Sent from the PostgreSQL - novice mailing list archive at Nabble.com.