Início / dba / Perguntas / 279579
Accepted
Jez
Asked: 2020-11-13 04:55:19 +0800 CST

O SQL Server verificará as restrições de FK entre exclusões de linha em uma única instrução DELETE?

  • 772

Eu tenho uma tabela que é auto-referenciada:

CREATE TABLE [dbo].[TestTable] (
    [id] [bigint] IDENTITY(100000,1) NOT NULL,
    [referenced_id] [bigint] NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED (
        [id] ASC
    ) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[TestTable] WITH NOCHECK ADD CONSTRAINT [FK_ReferencedId] FOREIGN KEY ([referenced_id])
    REFERENCES [dbo].[TestTable] ([id])
GO

ALTER TABLE [dbo].[TestTable] CHECK CONSTRAINT [FK_ReferencedId]
GO

Digamos que eu tenha várias linhas que fazem referência umas às outras:

id     | referenced_id
-------|--------------
100023 | 100024
100024 | 100023
100025 | 100026
100026 | 100023

Se eu tentar e DELETEa linha WHERE [id] = 100023, o FK será violado porque 100024e 100026referenciar essa linha e DELETEfalhará. No entanto, se eu apenas DELETE FROM [dbo].[TestTable], parece funcionar e excluir com êxito todas as linhas. Portanto, o SQL Server só parece estar verificando a restrição FK depois que todas as linhas a serem excluídas em uma única DELETEinstrução foram excluídas, em vez de entre cada exclusão de linha.

Posso confiar nesse comportamento, ou DELETEàs vezes isso pode falhar?

t-sql sql-server-2019

1 respostas

  1. Best Answer

    O SQL Server só parece estar verificando a restrição FK depois que todas as linhas a serem excluídas em uma única instrução DELETE foram excluídas, em vez de entre cada exclusão de linha. Posso confiar neste comportamento?

    Sim. As restrições são verificadas para a instrução, não para cada linha e não no final de uma transação.

    Aqui está o plano de consulta para um DELETE dessa tabela.

    insira a descrição da imagem aqui

    A exclusão ocorre na ramificação superior, mas as IDs das linhas excluídas são colocadas em spool e, após a etapa de exclusão do índice clusterizado, as IDs em spool são usadas para pesquisar todas as linhas que fazem referência às linhas excluídas. Se algum for encontrado, DELETE será revertido (instruções DML sempre são executadas em uma transação aninhada internamente) e um erro FK será gerado.

    • 4

relate perguntas