I have a table that is self-referencing:
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
Let's say I have several rows that reference each other:
id | referenced_id
-------|--------------
100023 | 100024
100024 | 100023
100025 | 100026
100026 | 100023
If I try and DELETE
the row WHERE [id] = 100023
, the FK will be violated because 100024
and 100026
reference that row and the DELETE
will fail. However if I just DELETE FROM [dbo].[TestTable]
, it seems to work and successfully delete all rows. Therefore, SQL Server only seems to be checking the FK constraint after all rows to be deleted in a single DELETE
statement have been deleted, rather than in between each row deletion.
Can I rely on this behaviour, or might such a DELETE
sometimes fail?
Best Answer
Yes. Constraints are checked for the statement, not for each row, and not at the end of a transaction.
Here's the query plan for a DELETE from that table.
The delete occurs on the top branch, but the IDs of the deleted rows are spooled, and after the clustered index delete step, the spooled IDs are used to look up any rows that reference the deleted rows. If any are found the DELETE will be rolled back (DML statements always run in a nested transaction internally), and a FK error raised.