no unique constraint matching given keys for referenced table - Mailing list pgsql-novice

From Lonni J Friedman
Subject no unique constraint matching given keys for referenced table
Date
Msg-id AANLkTi=GhUmFRKoS7BFuobqg9k9DncTjXK4gdK2eiUxT@mail.gmail.com
Whole thread Raw
Responses Re: no unique constraint matching given keys for referenced table  (Tom Lane <tgl@sss.pgh.pa.us>)
List 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

pgsql-novice by date:

Previous
From: John DeSoi
Date:
Subject: Re: importing a data file without defining a table structure
Next
From: Tom Lane
Date:
Subject: Re: no unique constraint matching given keys for referenced table