I have a postgresql table where I want to delete rows beyond a certain age. But the catch is that some foreign key constraints would be violated
For instance, if I run DELETE FROM links_reply WHERE id=3;
, I get a violates foreign key constraint error.
But when I run DELETE FROM links_groupseen WHERE which_reply_id=3;
and then run DELETE FROM links_reply WHERE id=3;
, everything's fine. Delete the child first, then the parent. Works!
Things get complicated though, when the respective tables are huge, and I'm trying to delete all rows before a certain date. E.g. I want to DELETE FROM links_reply WHERE "submitted_on" < now() - interval '30 days';
. In this particular case, how do I take care of 'the child table' first? What command do I run?
Best Answer
First start a transaction:
Start deleting all from links_groupseen first:
and then delete from links_reply:
and if you're happy with what you see, you can COMMIT. The reason I'm using a transaction is that within a transaction, now() will give you the same values at each call. Otherwise, you would have to replace now with a constant MYDATE value of your choice on both delete statements.