1

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 |
+----+---------+
users 
+----+
| 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.

James Stonehill
  • 1,125
  • 10
  • 22
  • 1
    Question would be why create something like that when cascade delete can do it for you – George Joseph Nov 23 '18 at 11:47
  • 1
    The db already has data in it and the data does not respect referential integrity. – James Stonehill Nov 23 '18 at 11:48
  • 1
    What does "referenced" mean without a FK declaration? What does "constraint/trigger" mean here, since a non-FK constraint doesn't "cause the referenced row in users to be deleted"? Use enough words sentences & references to parts of examples to be clear. Please read & act on [mcve]. Please clarify via post edits, not comments. – philipxy Nov 23 '18 at 12:11
  • This is a faq. Please always google many clear, concise & specific versions/phrasings of your question/problem/goal with & without your particular strings/names & read many answers. Add relevant keywords you discover to your searches. If you don't find an answer then post, using 1 variant search as title & keywords for tags. See the downvote arrow mouseover text. When you do have a non-duplicate code question to post please read & act on [mcve]. – philipxy Nov 23 '18 at 12:12
  • Can you point me to a question that you think is the same as my question please @philipxy? – James Stonehill Nov 23 '18 at 12:34
  • Also, @philipxy feel free to suggest edits to the question if you think the wording could be improved. – James Stonehill Nov 23 '18 at 12:37
  • 2
    I adjusted the sample table data to visualize "_The db already has data in it and the data does not respect referential integrity_". – jarlh Nov 23 '18 at 13:18
  • What should happen if a user has more than one post, and you delete only one of the posts? – joop Nov 23 '18 at 13:22
  • @joop fair question, but don't worry about that. I'll accept a solution that handles this situation in any way you want. – James Stonehill Nov 23 '18 at 13:37
  • Possible duplicate of [Trigger to delete rows from related tables before deleting rows from actual table](https://stackoverflow.com/q/20035105/3404097) – philipxy Nov 24 '18 at 03:37

1 Answers1

0

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();
  • Should be the other way around I think, if one takes "that causes the referenced row in users to be deleted when I delete a row in posts". You'd want the FK and cascade to be in the `users` table. – Damien_The_Unbeliever Nov 23 '18 at 13:51