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.