|
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 |
