[BUGS] 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
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
Re: [BUGS] spurious "UNIQUE constraint matching given keys for referenced table" error
> 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 ---(end of broadcast)--- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
Re: [BUGS] possible INSERT bug
i have the following utility function, which I use to easily return the OID
of an the new row created by an INSERT query:
---
CREATE FUNCTION insert_record_return_oid(text) RETURNS int4 AS
' DECLARE
s_query ALIAS FOR $1;
oid int4;
BEGIN
EXECUTE s_query;
GET DIAGNOSTICS oid = RESULT_OID;
RETURN oid;
END;
' LANGUAGE 'plpgsql' with (ISSTRICT);
---
Which correctly returns the OID of the inserted row.
usage example:
---
insert_record_return_oid('insert into sys_states (s_state) values(''po'')
');
---
However, if I get tricky, and imbed this into a select to return the
inserted row, I get an error:
---
select * from sys_states
where oid= insert_record_return_oid('insert into sys_states (s_state)
values(''po'') ');
---
"Cannot insert duplicate key" and the insert query never happens.
This is not a problem - I just do things another way, but I was wondering
what caused this?
Cheers,
Mathew
postgresql 7.2, btw
---(end of broadcast)---
TIP 3: if posting/reading through Usenet, please send an appropriate
subscribe-nomail command to [EMAIL PROTECTED] so that your
message can get through to the mailing list cleanly
[BUGS] Delete triggers
I have had a lot of trouble getting a DELETE
trigger to do nothing (ie let the delete operation occur instead of cancelling
it, as required)
The documentation on this is very thin on the
ground - I`ve just spend 4 Hours googling and the best I could find was one of
the main developers (Bruce?? sorry - too long ago) replying to an email in
2001. Which was to NOT cancel the delete operation, you need
to return NEW or OLD.
- If I return NULL the operation is
cancelled. ("DELETE 0")
- If I return NEW the operation is
cancelled. ("DELETE 0") Now NEW is not set for a delete (because it would
make no sense) so I am thinking this is the same as returning NULL
- If I return OLD the operation is cancelled
("DELETE 0").
I am using the 7.2 version.
To me this is either a bug in the system, or a
'bug' in the documentation. Look forward to hearing from
you.
Cheers,
Mathew
Re: [BUGS] Delete triggers
> "Mathew Frank" <[EMAIL PROTECTED]> writes:
> > The documentation on this is very thin on the ground - I`ve just spend 4
Ho=
> > urs googling and the best I could find was one of the main developers
(Bruc=
> > e?? sorry - too long ago) replying to an email in 2001.Which was to
NOT=
> > cancel the delete operation, you need to return NEW or OLD.
>
> There is no NEW row in a delete trigger.
Actually I think I just said that ;-)
> RETURN OLD should work.
> > - If I return OLD the operation is cancelled ("DELETE 0").
>
> I suspect pilot error.
Fair enough. Here is my test code (apologies - should have sent it the
first time):
-
CREATE FUNCTION "trg_test"() RETURNS "opaque" AS '
DECLARE
is_closed bool;
result record;
BEGIN
is_closed := false;
IF is_closed THEN
RAISE NOTICE ''Operation Cancelled: Month has been closed'';
return NULL;
ELSE
IF ( TG_OP = ''DELETE'' ) THEN
RAISE NOTICE ''Operation NOT cancelled'';
return OLD;
ELSE
RAISE NOTICE ''Operation NOT cancelled - NOT delete'';
return NEW;
END IF;
END IF;
END;
' LANGUAGE 'plpgsql';
drop trigger "protectperiod_montly_figures" on monthly_figures;
CREATE TRIGGER "protectperiod_montly_figures" BEFORE INSERT OR DELETE OR
UPDATE ON "monthly_figures" FOR EACH ROW EXECUTE PROCEDURE trg_test();
-
and the result of a delete query:
NOTICE: Operation NOT cancelled
ERROR: fmgr_info: function 1455898: cache lookup failed
(I was sure I was getting a 'Delete 0' but since my computer has crashed
since - I`m not sure. Maybe I was getting the above)
Now before you ask - the trigger was created after the trigger function.
I don`t see what can be wrong with the above - my code does not touch OLD -
merely returns it.
Cheers,
Mathew
ps - if you think I should move this to users I will, though at this point I
don`t see a code issue (I hope you do though)
---(end of broadcast)---
TIP 4: Don't 'kill -9' the postmaster
