I have 3 tables say A, B, C
CREATE TABLE A
(
A_id integer NOT NULL,
CONSTRAINT A_pkey PRIMARY KEY (A_id)
)
;
CREATE TABLE B
(
B_id serial NOT NULL,
A_id integer NOT NULL,
CONSTRAINT B_pkey PRIMARY KEY (B_id),
CONSTRAINT fkd08b6eeeb4f3a730 FOREIGN KEY (A_id)
REFERENCES A(A_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
;
CREATE TABLE C
(
C_id serial NOT NULL,
B_id integer NOT NULL,
CONSTRAINT C_pkey PRIMARY KEY (C_id),
CONSTRAINT fk31a4865653f36b09 FOREIGN KEY (B_id)
REFERENCES B(B_id) MATCH SIMPLE
ON UPDATE NO ACTION ON DELETE NO ACTION
)
;
Insert into A values (1);
Insert into B values (1,1);
Insert into C values (1,1);
I try this query -
delete from B where B_id = 1;
As expected I get this error -
ERROR: update or delete on table "b" violates foreign key constraint "fk31a4865653f36b09" on table "c"
DETAIL: Key (b_id)=(1) is still referenced from table "c".
********** Error **********
ERROR: update or delete on table "b" violates foreign key constraint "fk31a4865653f36b09" on table "c"
SQL state: 23503
Detail: Key (b_id)=(1) is still referenced from table "c".
Now if I have this delete within a function
CREATE OR REPLACE FUNCTION delete_B(id integer)
RETURNS void AS
$BODY$
declare
vSql varchar;
BEGIN
delete from B where id = id;
END;
$BODY$
LANGUAGE 'plpgsql' VOLATILE
COST 100;
And I try to execute this –
select delete_B(1)
This even with no cascade delete, will succeed and child records are deleted from C. This does not happen on all my set ups but has been happening consistently on more than one. Any known issue here?
-mridula
