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