The following bug has been logged on the website:
Bug reference: 14114
Logged by: Vladimir Zhavoronkov
Email address: zha-vova@yandex.ru
PostgreSQL version: 9.4.5
Operating system: Red Hat 4.8.5-4 64-bit
Description:
After creating a FK constraint with "on delete cascade" option I got a
problem with before-delete-trigger on a dependent table. This short listing
below will make it clear:
test_db=# create table t (id integer primary key);
CREATE TABLE
test_db=# create table tt (id integer primary key, t_id integer references
t(id) on delete cascade);
CREATE TABLE
--here we have table "t" that is referenced from table "tt" with
on-delete-cascade foreign key
--this before-trigger will prevent deleting rows from table "tt"
test_db=# create or replace
test_db-# function d_tt()
test_db-# returns trigger as $$
test_db$# begin
test_db$# return null;
test_db$# end;
test_db$# $$ language plpgsql;
CREATE FUNCTION
test_db=# create trigger d_tt before delete on tt for each row execute
procedure d_tt();
CREATE TRIGGER
--insert some data in tables:
test_db=# insert into t(id) values(1);
INSERT 0 1
test_db=# insert into tt(id, t_id) values(1,1);
INSERT 0 1
--so we have a record in table "tt" that references table "t"; then we try
to delete referenced record from table "t":
test_db=# delete from t where id = 1;
DELETE 1
Now we deleted record with id=1 from table "t", but still got records in
table "tt" with t_id=1. This contradicts with our foreign key constraint:
test_db=# select * from t;
id
----
(0 rows)
test_db=# select * from tt;
id | t_id
----+------
1 | 1
(1 row)