Thread: spurious "UNIQUE constraint matching given keys for referenced table" error

Hello people,
I'm a newbie to this list (though I've been hanging around on the ODBC list
for some time and I've been working with pgSQL for about 8months) so go
easy? ;-)

I realise this error is to stop a bad foreign key reference being created.
However I have a table with a multi-column primary key, and no matter what I
do I cannot create the FK to it from another table.  I have tried adding a
unique index and this makes no difference.    Having spent a lot of time
researching this, as I understand it either of these should stop this
message appearing.

I do have other tables in my db with these multiple-column keys and FKs to
them with no ill effects.

I am using 7.2, compiled  686 optimised, with GCC 2.96

Can anyone offer a suggestion as to a work around - or of course tell me I'm
and idiot and I've overlooked something ;-)   I have tried dumping and
reloading the database a number of times.

DEFINITIONS:
==============
CREATE TABLE "price_lists" (
"s_fk_price_list_id" char(3) NOT NULL,
"d_effective_date" date NOT NULL,
"s_caption" varchar(30),
"s_fk_price_list_include" char(3),
CONSTRAINT "price_lists_pkey" PRIMARY KEY ("s_fk_price_list_id",
"d_effective_date"),
CONSTRAINT "fk_price_lists2_fk" FOREIGN KEY ("s_fk_price_list_id")
REFERENCES "price_list_base" ("s_price_list_id") ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE,
CONSTRAINT "fk_price_lists_fk" FOREIGN KEY ("s_fk_price_list_include")
REFERENCES "price_list_base" ("s_price_list_id") ON DELETE NO ACTION ON
UPDATE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE
) WITH OIDS;

CREATE TABLE "price_list_items" (
"s_fk_item_id" char(8) NOT NULL,
"s_fk_price_list_id" char(3) NOT NULL,
"d_fk_effective_date" date NOT NULL,
"f_threshold_price" numeric(7, 2) NOT NULL,
"m_price" money,
CONSTRAINT "price_list_items_pkey" PRIMARY KEY ("s_fk_item_id",
"s_fk_price_list_id", "d_fk_effective_date", "f_threshold_price")
) WITH OIDS;

CODE THAT PRODUCES ERROR:
==========================
alter table price_list_items
ADD FOREIGN KEY (d_FK_Effective_Date, s_FK_Price_List_ID)
REFERENCES Price_Lists (d_Effective_Date, s_Price_List_ID);

EXACT ERROR:
============
UNIQUE constraint matching given keys for referenced table "price_lists" not
found


Thanks in advance,
Cheers,
Mathew

"Mathew Frank" <mathewfrank@qushi.com> writes:
> UNIQUE constraint matching given keys for referenced table "price_lists" not
> found

As of CVS tip your example gives

ERROR:  ALTER TABLE: column "s_price_list_id" referenced in foreign key constraint does not exist

which appears correct (you misspelled the column name).

7.2 does foreign key validity checking in a funny order that causes it
to produce the other error message first.  While not incorrect, it's
sure misleading :-(

            regards, tom lane

> which appears correct (you misspelled the column name).
>
> 7.2 does foreign key validity checking in a funny order that causes it
> to produce the other error message first.  While not incorrect, it's
> sure misleading :-(

Thanks a bunch.

I'm always a bit nervous about calling anything a bug... 9/10 is caused by a
typo or something on behalf of the user ;)

Cheers,
Mathew

"Mathew Frank" <mathewfrank@qushi.com> writes:
> I'm always a bit nervous about calling anything a bug... 9/10 is caused by a
> typo or something on behalf of the user ;)

Well, it's a two-way street: typos and other sorts of mistakes tend to
stress paths that the developers would've never thought to test.
Besides, I'm a firm believer in the notion that software should deliver
useful error messages, and PG definitely wasn't getting that done in
this case.  So complain away.  I'm glad to be able to say "we already
fixed that for the next release", but that won't always be true...

            regards, tom lane