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

From: "Mathew Frank" <mathewfrank(at)qushi(dot)com>
To: <pgsql-bugs(at)postgresql(dot)org>
Subject: spurious "UNIQUE constraint matching given keys for referenced table" error
Date: 2002-10-15 07:20:53
Message-ID: 042c01c2741b$6725e170$0a00a8c0@dax
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-bugs

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

Responses

Browse pgsql-bugs by date

  From Date Subject
Next Message pgsql-bugs 2002-10-15 11:23:24 Bug #797: Inaccurate in PostgreSQL 7.3b2 Developer's Guide
Previous Message shuaimeng 2002-10-14 13:10:49 help