no unique constraint matching given keys for referenced table

From: Lonni J Friedman <netllama(at)gmail(dot)com>
To: pgsql-novice(at)postgresql(dot)org
Subject: no unique constraint matching given keys for referenced table
Date: 2010-08-03 21:08:22
Message-ID: AANLkTi=GhUmFRKoS7BFuobqg9k9DncTjXK4gdK2eiUxT@mail.gmail.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-novice

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

Responses

Browse pgsql-novice by date

  From Date Subject
Next Message Tom Lane 2010-08-03 21:35:12 Re: no unique constraint matching given keys for referenced table
Previous Message Sumeet Jauhar 2010-07-31 05:33:01 Urgent help needed Question on PQexec