Deleting records from tables containing FK pointing to each other

Question

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:
    TABLE "links_publicreply" CONSTRAINT "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED

---

 Table "public.links_publicreply"
     Column      |           Type           |                           Modifiers                            
-----------------+--------------------------+----------------------------------------------------------------
 id              | integer                  | not null default nextval('links_publicreply_id_seq'::regclass)
 submitted_by_id | integer                  | not null
 answer_to_id    | integer                  | not null
 submitted_on    | timestamp with time zone | not null
 description     | text                     | not null
Indexes:
    "links_publicreply_pkey" PRIMARY KEY, btree (id)
    "links_publicreply_answer_to_id" btree (answer_to_id)
    "links_publicreply_submitted_by_id" btree (submitted_by_id)
Foreign-key constraints:
    "links_publicreply_answer_to_id_fkey" FOREIGN KEY (answer_to_id) REFERENCES links_link(id) DEFERRABLE INITIALLY DEFERRED
    "links_publicreply_submitted_by_id_fkey" FOREIGN KEY (submitted_by_id) REFERENCES auth_user(id) DEFERRABLE INITIALLY DEFERRED
Referenced by:
    TABLE "links_link" CONSTRAINT "publicreplyposter_link_fkey" FOREIGN KEY (latest_reply_id) REFERENCES links_publicreply(id) ON UPDATE CASCADE ON DELETE CASCADE

How do I delete records from the table Link under this scenario? And from Publicreply? Im using postgresql 9.3.10.

Answer 1

The problem can be solved with many ways:

1. 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 ;
   

2. 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 ;
   

3. 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?