spurious "UNIQUE constraint matching given keys for referenced table" error
От | Mathew Frank |
---|---|
Тема | spurious "UNIQUE constraint matching given keys for referenced table" error |
Дата | |
Msg-id | 042c01c2741b$6725e170$0a00a8c0@dax обсуждение исходный текст |
Ответы |
Re: spurious "UNIQUE constraint matching given keys for referenced table" error
|
Список | 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
В списке pgsql-bugs по дате отправления: