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
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?