This was originally logged under pgsql-sql, as "[SQL] Referential Integrity", but after some discussion it seems that this is a bug.
I have the following scenario in pg7.3.1.:
CREATE TABLE alex (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
itemdesc varchar(100),
CONSTRAINT alex_pkey PRIMARY KEY (aid, bid));
CREATE TABLE alexette (aid varchar(40) NOT NULL,
bid varchar(100) NOT NULL,
vcode varchar(1),
CONSTRAINT alexette_pkey PRIMARY KEY (aid, bid));
ALTER TABLE alexette
ADD CONSTRAINT alexette_fk_1 FOREIGN KEY (aid,bid)
REFERENCES alex (aid,bid)
ON DELETE CASCADE;
INSERT INTO alex VALUES ('1','1','OneOne');
INSERT INTO alexette VALUES ('1','1','V');
CREATE FUNCTION test() RETURNS INT4 AS '
DECLARE
v_return INTEGER;
BEGIN
DELETE FROM alex
WHERE aid = ''1''
AND bid = ''1'';
INSERT INTO alex (aid,bid,itemdesc)
VALUES (''1'',''1'',''OneOne'');
INSERT INTO alexette (aid,bid,vcode)
VALUES (''1'',''1'',''V'');
RETURN 0;
END;
' LANGUAGE 'plpgsql';
Please could someone tell me why running the function test() causes the following error:
"Cannot insert a duplicate key into unique index alexette_pkey"
The foreign key with "on delete cascade" should ensure that the delete statement within this function should delete the records from both alex and alexette. Indeed a simple "delete from alex" demonstrates that this does indeed work. However, it appears that within the function this is not happening or that the insert statements cannot see the full extent of the changes made by the delete statement. Whichever, surely this is wrong?
Regards,
Alexander Stanier.
mailto:[EMAIL PROTECTED]
