I want to setup a cascading delete between two tables without enforcing referential integrity.
For example, imagine I've got the following two tables:
posts+----+---------+
| id | user_id |
+----+---------+
| 1 | 1 |
| 2 | 2 |
| 3 | 3 |
+----+---------+
+----+
| id |
+----+
| 1 |
| 4 |
+----+
I want to create a constraint/trigger that causes the referenced row in users to be deleted when I delete a row in posts without enforcing referential integrity.
You will need a trigger for that:
create function delete_users()
returns trigger
as '
begin
delete from users where id = old.user_id;
return null;
end;
' language plpgsql;
create trigger trg_delete_users
after delete on posts
for each row execute procedure delete_users();
Online example: https://rextester.com/MJFCW22289
If you are on Postgres 10 or newer a statement level trigger might faster:
create function delete_users()
returns trigger
as $trg$
begin
delete from users where id = (select user_id from old_table);
return null;
end;
$trg$
language plpgsql;
create trigger trg_delete_users
after delete on posts
referencing old table as old_table
for each statement
execute procedure delete_users();
users table.