Início / dba / Perguntas / 164821
Accepted
Hassan Baig
Asked: 2017-02-20 03:19:27 +0800 CST

Excluindo registros de tabelas contendo FK apontando um para o outro

  • 772

Background: Um pouco de um DBA acidental aqui. Eu tenho um site semelhante ao reddit onde os usuários enviam links apontando para vários conteúdos da Internet e podem deixar comentários em cada postagem. Este aplicativo - vamos chamá-lo de Links - tem duas tabelas correspondentes para armazenar dados: linke publicreply(ou seja, comentários associados a cada link).

Problema: não consigo excluir registros (para manutenção) dessas duas tabelas devido a restrições FK interdependentes. Precisa de orientação para resolver a situação.

Detalhes: Cada Publicreplyobjeto armazena um FK para o Linkobjeto ao qual está associado. Além disso, cada Linkobjeto também salva uma referência à última resposta pública associada a ele. Isso cria uma situação em que todos os Publicreplyobjetos têm um LinkFK e vice-versa. Como em:

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

Como excluo registros da tabela Linknesse cenário? E de Publicreply? Estou usando o postgresql 9.3.10.

postgresql postgresql-9.3

1 respostas

  1. Best Answer

    O problema pode ser resolvido de várias maneiras:

    1. Primeiro, notamos que uma das colunas FK é anulável. Isso permite excluir de ambas as tabelas, usando três instruções em uma única transação e sem necessidade de restrições adiáveis. Primeiro atualize latest_reply_idpara null, depois delete de PublicReply, depois de 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. Então notamos que uma das restrições de chave estrangeira já está definida como adiável. Isso permite excluir de ambas as tabelas, usando duas instruções em uma única transação:

      -- 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. Usando CTEs modificáveis, podemos excluir de ambas as tabelas em uma única instrução. As restrições não precisam ser adiadas para isso. Exemplo:

      -- 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) ;

    A exclusão de PublicReplyserá um pouco mais complicada, dependendo dos requisitos, mas pode ser feita com qualquer um dos métodos acima. Quais são os requisitos?

    • excluir um PublicReply, seu pai Linke todas as respostas associadas?

    • delete a PublicReplye se for a resposta mais recente, altere o pai Linkpara apontar para a resposta anterior? Se for o único, defina-o como NULL?

    • delete a PublicReplye se for a resposta mais recente, altere o pai Linkpara apontar para a resposta anterior? Se for o único, excluir o pai Linktambém?

    • 8

relate perguntas