Exclusões em cascata e auto-referência no SQL Server

Oi Pessoal,

Ainda estou me acostumando com a mudança do blog (algumas coisas melhoraram muito, mas estou sentindo muita falta de outras). Iniciando o primeiro post do ano de 2011 (e o primeiro que não foi importado do Live), vou apontar uma rápida solução para uma thread que vi no fórum do MSDN. A thread referia-se a impossibilidade de utilizar a exclusão em cascata para auto-relacionamentos. Admito que nunca precisei fazer algo desse tipo, pois, normalmente a exclusão em cascata não é utilizada e quase sempre tabelas que se auto-relacionam são tabelas de referência e não são apagadas. Ainda assim, vejamos o que pode ser feito nessa situação.

— Cria uma tabela de Empregados
CREATE TABLE Empregados (
    EmpregadoID INT NOT NULL,
    EmpregadoNome VARCHAR(50) NOT NULL,
    SuperiorID INT NULL)

— Cria as Constraints
ALTER TABLE Empregados ADD CONSTRAINT PK_Empregado PRIMARY KEY (EmpregadoID)
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
    FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)

— Insere alguns registros
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (1,‘Sálvio’,NULL)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (2,‘Hélio’,1)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (3,‘Dênis’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (4,‘Júnior’,2)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (5,‘Marcos’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (6,‘Bruno’,3)
INSERT INTO Empregados (EmpregadoID, EmpregadoNome, SuperiorID) VALUES (7,‘Paulo’,5)

Podemos ver as seguintes relações hierárquicas:

  • Sálvio -> Hélio
    Sálvio -> Hélio -> Dênis
    Sálvio -> Hélio -> Dênis -> Marcos
    Sálvio -> Hélio -> Dênis -> Marcos -> Paulo
    Sálvio -> Hélio -> Júnior
  • Hélio -> Dênis
    Hélio -> Dênis -> Marcos
    Hélio -> Dênis -> Marcos -> Paulo
    Hélio -> Júnior
  • Dênis -> Marcos
    Dênis -> Marcos -> Paulo
  • Marcos -> Paulo

Agora que os registros estão populados, vamos tentar efetuar uma exclusão:

— Tenta excluir o registro do empregado Hélio
DELETE FROM Empregados WHERE EmpregadoID = 2

Como era de se esperar, a existência de subordinados ao empregado Hélio representadas pelo auto-relacionamento gerou um erro na exclusão:

Msg 547, Level 16, State 0, Line 2
The DELETE statement conflicted with the SAME TABLE REFERENCE constraint “FK_Empregado_Empregado”. The conflict occurred in database “tempdb”, table “dbo.Empregados”, column ‘SuperiorID’.
The statement has been terminated.

Admitindo que a exclusão de um empregado deva excluir também os seus subordinados, poderíamos ativar a exclusão em cascata.

— Remove a constraint FK anterior
ALTER TABLE Empregados DROP CONSTRAINT FK_Empregado_Empregado

— Recria a constraint com a exclusão em cascata
ALTER TABLE Empregados ADD CONSTRAINT FK_Empregado_Empregado
    FOREIGN KEY (SuperiorID) REFERENCES Empregados (EmpregadoID)
    ON DELETE CASCADE

A exclusão da constraint ocorre com sucesso, mas a criação com a exclusão em cascata e a auto-referência gera um erro:

Msg 1785, Level 16, State 0, Line 2
Introducing FOREIGN KEY constraint ‘FK_Empregado_Empregado’ on table ‘Empregados’ may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.
Msg 1750, Level 16, State 0, Line 2
Could not create constraint. See previous errors.

No SQL Server, o recurso de exclusão em cascata possui algumas restrições e uma delas é não poder ser utilizado para constraints que envolvam o auto-relacionamento. Não cheguei a testar esse comportamento em outros SGBDs como o ORACLE, DB2 e afins, mas entendo que essa restrição do SQL Server tenha certo fundamento. A exclusão em cascata com o auto-relacionamento pode levar a situações complexas de gerenciar bem como efeitos catastróficos. Excluir o registro do Sálvio por exemplo, incorreria em praticamente limpar a tabela de empregados se a exclusão em cascata funcionasse com o auto-relacionamento. Outra situação envolve referências circulares. Digamos que não estejamos tratando de uma relação de subordinação, mas de indicações ou até um amigo oculto. Em tais situações é bem fácil de acontecer uma referência circular do tipo K -> X -> Y -> Z -> K. A exclusão em cascata em uma situação dessas iria provocar um loop infinito.

Admitindo-se que não haja um ciclo, como fazer então para excluir em cascata registro de uma tabela que possui auto-relacionamento se a FK não dá essa possibilidade ? A solução que me veio a cabeça para resolver a situação é combinar a chave estrangeira com uma trigger de Instead Of. Só que há mais um detalhe, se a FK irá bloquear as exclusões que a violem, é preciso excluir os registros na ordem correta. Para fazer isso utilizei-me de uma CTE recursiva.

— Captura o ID do empregado Hélio
DECLARE @EmpregadoID INT
SET @EmpregadoID = (
    SELECT EmpregadoID FROM Empregados
    WHERE EmpregadoNome = ‘Hélio’)

— Lista todos os subordinados ao Hélio com o nível
;WITH Emp (EmpregadoID, EmpregadoNome, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID = @EmpregadoID
    UNION ALL
    SELECT E.EmpregadoID, E.EmpregadoNome, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Recupera os subordinados ao Hélio
SELECT EmpregadoID, EmpregadoNome, SuperiorID, Nivel FROM Emp

EmpregadoID EmpregadoNome SuperiorID Nivel
2 Hélio 1 1
3 Dênis 2 2
4 Júnior 2 2
5 Marcos 3 3
6 Bruno 3 3
7 Paulo 5 4

Agora ficou bem mais fácil. A CTE recursiva retorna o registro e todos os subordinados. Para evitar violação de chave estrangeira, basta excluir do maior nível para o menor nível. Adicionei a lógica dentro da trigger.

— Cria uma trigger Instead Of Delete
CREATE TRIGGER trgIDEmpregados ON Empregados
INSTEAD OF DELETE
As

— Cria uma tabela temporária para armazenar os registros a excluir
DECLARE @Empregados TABLE (EmpregadoID INT, Nivel INT)

— Monta a CTE recursiva
;WITH Emp (EmpregadoID, SuperiorID, Nivel)
As (
    SELECT EmpregadoID, EmpregadoNome, SuperiorID, 1 As Nivel
    FROM Empregados
    WHERE EmpregadoID IN (SELECT EmpregadoID FROM Deleted)
    UNION ALL
    SELECT E.EmpregadoID, E.SuperiorID, Nivel + 1
    FROM Empregados As E
    INNER JOIN Emp ON E.SuperiorID = Emp.EmpregadoID)

— Insere os IDs dos registros a excluir
INSERT INTO @Empregados SELECT EmpregadoID, Nivel FROM Emp
ORDER BY Nivel DESC

— Exclui os registros
DECLARE @MaiorNivel INT
SET @MaiorNivel = (SELECT MAX(Nivel) FROM @Empregados)

WHILE EXISTS (SELECT * FROM @Empregados)
BEGIN
    — Exclui os empregados
    DELETE FROM Empregados WHERE EmpregadoID IN
        (SELECT EmpregadoID FROM @Empregados WHERE Nivel = @MaiorNivel)

    — Exclui os empregados da tabela temporária
    DELETE FROM @Empregados WHERE Nivel = @MaiorNivel

    — Decrementa um nível
    SET @MaiorNivel = @MaiorNivel – 1

END

Agora vamos ver alguns testes:

— Exclui o Hélio
— Só deve sobrar o Sálvio
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 2
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Dênis
— Só deve sobrar o Sálvio, Hélio e Júnior
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 3
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Marcos e o Júnior
— Só deve sobrar o Sálvio, Dênis e Bruno
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID IN (3,5)
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

— Exclui o Sálvio
— Não sobra ninguém
BEGIN TRAN
    DELETE FROM Empregados WHERE EmpregadoID = 1
    SELECT EmpregadoID, EmpregadoNome, SuperiorID FROM Empregados
ROLLBACK

Como podemos ver, a trigger inclusive já é orientada a conjuntos permitindo múltiplas exclusões. Confesso que tirando a thread eu nunca tinha tido tal necessidade. Quem precisar é só adaptar.

[ ]s,

Gustavo

4 Respostas para “Exclusões em cascata e auto-referência no SQL Server

  1. Gustavo, ótimo exemplo.
    Isso tem a ver com o post que coloquei na MSDN Brasil a respeito de auto-referência, onde dei um exemplo de área e sub-áreas onde não conseguia excluir os filhos quando havia uma constraint de foreign key na tabela e você me disse que isso tinha a ver com referência em círculo?

    Gostei do exemplo!

  2. Demétrio Silva

    Excelente post Gustavo,

    O uso de CTE já me ajudou muito também, é um recurso que realmente não pode faltar num SGBD, sem contar no conhecimento teórico mostrado neste post.

    Abraço,

    Demétrio Silva

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s