6

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.

1 Answer 1

9

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?

1
  • Very indepth! Thanks for chiming in mate :-) I'm going to give it a shot and get back to you. Commented Feb 23, 2017 at 18:44

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.