Deleting records from tables containing FK pointing to each other

Background: Bit of an accidental DBA here. I have this reddit-like website where users submit links pointing to various internet content, and can then leave comments under each posting. This app - let's call it Links - has two corresponding tables to store data: link and publicreply (i.e. comments assoc. to each link).

Problem: I can't seem to delete records (for maintenance) from these two tables because of interdependent FK constraints. Need guidance to resolve the situation.

Details: Each Publicreply object stores a FK to the Link object it's associated to. Moreover, each Link object also saves a reference to the latest publicreply associated to it. This creates a situation whereby all Publicreply objects have a Link FK, and vice versa. As in:

```
Table "public.links_link"
Column | Type | Modifiers
----------------------+--------------------------+---------------------------------------------------------
id | integer | not null default nextval('links_link_id_seq'::regclass)
description | text | not null
submitter_id | integer | not null
submitted_on | timestamp with time zone | not null
url | character varying(250) | not null
image_file | character varying(100) |
reply_count | integer | default 0
latest_reply_id | integer |
is_visible | boolean | default true
Indexes:
"links_link_pkey" PRIMARY KEY, btree (id)
"links_link_submitter_id" btree (submitter_id)
Foreign-key constraints:
"links_link_submitter_id_fkey" FOREIGN KEY (submitter_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
"publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE
Referenced by:
TAB

The problem can be solved with many ways:

-
First we notice that one of the FK columns is nullable. This allows to delete from both tables, using three statements in a single transaction and without need for deferrable constraints. First update latest_reply_id to null, then delete from PublicReply, then from Link:

-- Delete one (or more) `Link` rows 
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    UPDATE public.links_link
    SET latest_reply_id = NULL
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted

    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted
COMMIT ;

-
Then we notice that one of the foreign key constraints is already defined as deferrable. This allows to delete from both tables, using two statements in a single transaction:

-- Delete one (or more) `Link` rows 
-- and all the `Publicreply` rows associated with them:
BEGIN ;
    DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?) ;              -- Link ids to be deleted

    DELETE FROM public.links_publicreply
    WHERE answer_to_id IN (?, ?, ..., ?) ;    -- Link ids to be deleted
COMMIT ;

-
Using modifiable CTEs, we can delete from both tables in a single statement. The constraints do not need to be deferred for this. Example:

-- Delete one (or more) `Link` rows 
-- and all the `Publicreply` rows associated with them:
WITH del_link AS
  ( DELETE FROM public.links_link
    WHERE id IN (?, ?, ..., ?)                -- Link ids to be deleted
    RETURNING id
  )
DELETE FROM public.links_publicreply
WHERE answer_to_id IN (TABLE del_link) ;

Deleting from PublicReply will be a bit more complicated, depending the requirements but can be done with any of the above methods. What are the requirements?

-
delete a PublicReply, its parent Link and all the associated replies?

-
delete a PublicReply and if it's the latest reply, change the parent Link to point to the previous reply? If it's the only one, set it to NULL?

-
delete a PublicReply and if it's the latest reply, change the parent Link to point to the previous reply? If it's the only one, delete the parent Link as well?

StackExchange Database Administrators Q#164821, answer score: 9