PostgreSQL - delete all post reactions when deleting a post?

Question

I'm having trouble delete post having reactions (likes) because of the foreign key associating the post.id with the post_reaction.post_id.

I'd like to create a foreign key with CASCADE on delete, but because a post can have many post_reactions, I get a an error: no unique constraint matching given keys for referenced table "post_reactions"

I shouldn't think I'd have to delete everything individually on the client side, should I? i.e. delete all the post_reactions first, then the post?

  const handleDeletePost = () => {
    if (postImageRelayId) {
      ImageDeleteMutation(postImageRelayId); // if there is an image, delete it
    }
    if (postReactions.length) {
      PostReactionDeleteMutation(postReactions); // if there are reactions, delete all of them
    }
    PostDeleteMutation(post.id, currentPerson); // delete post itself
  };

The image table has a post_id column with an fkey to post.id

The post_reactions table has a post_id column also with an fkey to the post.id

I'd like to simply delete the post from the posts table and postgres CASCADE delete any reaction and / or image having that post_id, but, I'm unable to create a foreign key on the post table referencing post_reactions.post_id.

Please add sample data to your question to make it clear what workflow you want during deletion. — Tim Biegeleisen, Apr 14 '21 at 04:47
You need to have post.id marked as the PRIMARY KEY for it to work. See: https://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref — Jim, Apr 14 '21 at 04:51
Does this answer your question? [What is causing ERROR: there is no unique constraint matching given keys for referenced table?](https://stackoverflow.com/questions/11966420/what-is-causing-error-there-is-no-unique-constraint-matching-given-keys-for-ref) — Jim, Apr 14 '21 at 04:51
@TimBiegeleisen Kind thanks. I added my current delete function which calls three mutations. I'm not sure what specific data would help. — Kirk Ross, Apr 14 '21 at 05:07
@jim I saw that one. I need to learn more about keys and constraints because I'm stabbing in the dark as to what is possible and what is smart. — Kirk Ross, Apr 14 '21 at 05:09
bad arg in `PostReactionDeleteMutation(postReactions)` ? test mutations in playground BEFORE coding! — xadm, Apr 14 '21 at 06:36

score 1 · Answer 1 · answered Apr 14 '21 at 05:51

1

The foreign key has to point from post_reactions to posts, not the other way around, as your error message suggests you tried to do.

answered Apr 14 '21 at 05:51

Laurenz Albe

209,280
17
206
263

That's how it is. And if I try to delete a post without first deleting reactions which have that fkey relation to it, the delete fails. I was hoping there was a way to tell postgres to delete any rows in foreign tables which have an fkey pointing to the post's id. – Kirk Ross Apr 15 '21 at 04:23

score 0 · Answer 2 · answered Apr 15 '21 at 04:37

I had misunderstood CASCADE. I thought CASCADE needed to be on the post table not the post_reactions table.

I added it to the post_reactions_post_id fkey and it works now.

ALTER TABLE wuddit.post_reactions
    ADD CONSTRAINT post_reactions_post_id_fkey FOREIGN KEY (post_id)
    REFERENCES wuddit.post (id) MATCH SIMPLE
    ON UPDATE NO ACTION 
    ON DELETE CASCADE; // this

PostgreSQL - delete all post reactions when deleting a post?

2 Answers2