Início / dba / Perguntas / 153420
Accepted
Alon Eitan
Asked: 2016-10-27 12:49:52 +0800 CST

MySQL - Excluir linha que possui uma restrição de chave estrangeira que faz referência a si mesma

  • 772

Tenho uma tabela na qual armazeno todas as mensagens do fórum postadas pelos usuários no meu site. A estrutura de hierarquia de mensagens é implementada usando um modelo de conjunto aninhado .

A seguir, uma estrutura simplificada da tabela:

  • ID (CHAVE PRIMÁRIA)
  • Owner_Id (REFERÊNCIAS DE CHAVE ESTRANGEIRA PARA Id )
  • Parent_Id (REFERÊNCIAS DE CHAVE ESTRANGEIRA PARA Id )
  • esquerda
  • certo
  • nível

Agora, a tabela está mais ou menos assim:

+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| Id      | Owner_Id      | Parent_Id      | nleft      | nright      | nlevel      |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +
| 1       | 1             | NULL           | 1          | 8           | 1           |
| 2       | 1             | 1              | 2          | 5           | 2           |
| 3       | 1             | 2              | 3          | 4           | 3           |
| 4       | 1             | 1              | 6          | 7           | 2           |
+ ------- + ------------- + -------------- + ---------- + ----------- + ----------- +

Observe que a primeira linha é a mensagem raiz, e a árvore deste post pode ser exibida como:

-- SELECT * FROM forumTbl WHERE Owner_Id = 1 ORDER BY nleft;

MESSAGE (Id = 1)
    MESSAGE (Id = 2)
        Message (Id = 3)
    Message (Id = 4)

Meu problema ocorre quando tento excluir todas as linhas sob o mesmo Owner_Idem uma única consulta. Exemplo:

DELETE FROM forumTbl WHERE Owner_Id = 1 ORDER BY nright;

A consulta acima falha com o seguinte erro:

Código de erro: 1451. Não é possível excluir ou atualizar uma linha pai: uma restrição de chave estrangeira falha ( forumTbl, CONSTRAINT Owner_Id_frgnFOREIGN KEY ( Owner_Id) REFERENCES forumTbl( Id) ON DELETE NO ACTION ON UPDATE NO ACTION)

O motivo é que a primeira linha , que é o nó raiz ( Id=1), também tem o mesmo valor em seu Owner_Idcampo ( Owner_Id=1), e isso faz com que a consulta falhe devido à restrição de chave estrangeira.

Minha pergunta é: Como posso evitar essa circularidade de restrição de chave estrangeira e excluir uma linha que faz referência a si mesma? Existe uma maneira de fazer isso sem primeiro ter que atualizar o Owner_Idda linha raiz para NULL?

Eu criei uma demonstração deste cenário: http://sqlfiddle.com/#!9/fd1b1

Obrigada.

mysql database-design

3 respostas

  1. Best Answer

    1. Além de desabilitar as chaves estrangeiras, o que é perigoso e pode levar a inconsistências, há duas outras opções a serem consideradas:

    2. Modifique as FOREIGN KEYrestrições com a ON DELETE CASCADEopção. Eu não testei todos os casos, mas você certamente precisa disso para a (owner_id)chave estrangeira e possivelmente para o outro também.

      ALTER TABLE forum
    DROP FOREIGN KEY owner_id_frgn,
    DROP FOREIGN KEY parent_id_frgn ;
ALTER TABLE forum
    ADD CONSTRAINT owner_id_frgn
        FOREIGN KEY (owner_id) 
        REFERENCES forum (id)
        ON DELETE CASCADE,
    ADD CONSTRAINT parent_id_frgn
        FOREIGN KEY (parent_id) 
        REFERENCES forum (id)
        ON DELETE CASCADE ;

      Se você fizer isso, a exclusão de um nó e todos os descendentes da árvore será mais simples. Você exclui um nó e todos os descendentes são excluídos por meio das ações em cascata:

      DELETE FROM forum
WHERE id = 1 ;         -- deletes id=1 and all descendants

    3. O problema em que você entrou é, na verdade, 2 problemas. A primeira é que deletar de uma tabela com chave estrangeira auto-referenciada não é um problema sério para o MySQL, desde que não haja nenhuma linha que faça referência a si mesma. Se houver uma linha, como no seu exemplo, as opções são limitadas. Desative as chaves estrangeiras ou use a CASCADEação. Mas se não houver essas linhas, a exclusão se tornará um problema menor.

      Portanto, se decidirmos armazenar NULLem vez do mesmo idem owner_id, você poderá excluir sem desabilitar chaves estrangeiras e sem cascatas.

      Você então tropeçaria no segundo problema! Executar sua consulta geraria um erro semelhante:

      DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 ;

      Erro(s), aviso(s):
      Não é possível excluir ou atualizar uma linha pai: uma restrição de chave estrangeira falha (rextester.forum, CONSTRAINT owner_id_frgn FOREIGN KEY (owner_Id) REFERENCES forum (id))

      A razão para este erro seria diferente do que antes. É porque o MySQL verifica cada restrição depois que cada linha é excluída e não (como deveria) no final da instrução. Portanto, quando um pai é excluído antes que seu filho seja excluído, obtemos um erro de restrição de chave estrangeira.

      Felizmente, existe uma solução simples para isso, por causa do modelo de conjunto aninhado e para isso o MySQL nos permite definir uma ordem para as exclusões. Nós apenas temos que ordenar por nleft DESCou por nright DESC, o que garante que todos os filhos sejam excluídos antes de um pai:

      DELETE FROM forum 
WHERE owner_id = 1 OR id = 1 
ORDER BY nleft DESC ;

      Nota menor, podemos (ou devemos) usar uma condição que considere também o modelo aninhado. Isso é equivalente (e pode usar um índice (nleft, nright)para descobrir quais nós excluir:

      DELETE FROM forum 
WHERE nleft >= 1 AND nright <= 8 
ORDER BY nleft DESC ;
    • 11 
  2. SET FOREIGN_KEY_CHECKS=0;
DELETE FROM forum WHERE Owner_Id = 1 ORDER BY nright;
SET FOREIGN_KEY_CHECKS=1;

    apenas não esqueça neste caso Você deve analisar manualmente as situações quando parent_id mostrar para 1, porque você não usa cascata

    • 9

  3. Espero que essas respostas ajudem outras pessoas.

    Mostrar restrições de tabela

    Se você não conhece as restrições de tabela, isso ajudará.

    USE INFORMATION_SCHEMA;
SELECT TABLE_NAME,
       COLUMN_NAME,
       CONSTRAINT_NAME,
       REFERENCED_TABLE_NAME,
       REFERENCED_COLUMN_NAME
FROM KEY_COLUMN_USAGE
WHERE TABLE_SCHEMA = "database_name" 
      AND TABLE_NAME = "table_name" 
      AND REFERENCED_COLUMN_NAME IS NOT NULL;

    Para excluir dados de várias tabelas no banco de dados, é necessário eliminar as restrições primeiro e, em seguida, ADICIONAR CONSTRAINT.

    DROP FOREIGN KEY constraints_name

    O nome da restrição pode ser simplificado:

    1. table_name: endereços
    2. chave estrangeira: user_id
    3. adicionar palavra-chave _foreignno final

    Exemplo:

    ALTER TABLE addresses
    DROP FOREIGN KEY addresses_user_id_foreign;
ALTER TABLE addresses
    ADD CONSTRAINT addresses_user_id_foreign
        FOREIGN KEY (user_id) 
        REFERENCES users (id)
        ON DELETE CASCADE;

    Ref1: https://dev.mysql.com/doc/refman/8.0/en/create-table-foreign-keys.html Ref2: https://www.mysqltutorial.org/mysql-foreign-key/

    • 1

relate perguntas