Foreign Key Issue Prevents Delete

1

hi experts, I know this error means I have data in the other table that references the data I'm trying to delete but I don't know how to find the data. Thanks for any help.

This is my code:
SET QUOTED_IDENTIFIER ON;
Declare @CompareDate char(10)
Set @CompareDate = (Select CompareDate from OzTestCompareDate);
DELETE FROM Batches

FROM tblReturnDocumentBatches Batches

INNER JOIN tblQuote Q

ON Q.QuoteID = Batches.QuoteID

WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

FAILED The DELETE statement conflicted with the REFERENCE constraint "FK_tblReturnDocument_tblReturnDocumentBatches". The conflict occurred in database "AAA", table "dbo.tblReturnDocument", column 'ReturnDocumentBatchID'.

The FK looks like this:
ALTER TABLE [dbo].[tblReturnDocument] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocument_tblReturnDocumentBatches] FOREIGN KEY ( [ReturnDocumentBatchID] )

REFERENCES [dbo] . [tblReturnDocumentBatches] ( [ReturnDocumentBatchID] )

2

You've got to DELETE from the tblReturnDocumentBatches table first. You may need to store the QuoteIDs to be deleted in a separate table to allow you to do the DELETEs in the proper order.

3

Thanks @ScottPletcher. I tried delete from the tblReturnDocumentBatches table first and it failed with the same FK error.
When I query the FK system tables INFORMATION_SCHEMA.TABLE_CONSTRAINTS, etc. it shows
Table Dependency Level
tblReturnDocumentSelelction 5
tblReturnDocument 4
tblReturnDocumentBatches 3

(These 3 are related and easily get tangled up that's why I'm showing the other 2 tables).
So whether I delete tblReturnDocumentBatches first, or 3rd, it fails with the same FK error.
I'm at a loss as to how to resolve it.

4

If you need to automatically DELETE across all of them, the easiest way would be script out a DROP and CREATE of the FOREIGN KEY definitions. Change all the FK definitions to include the ON DELETE CASCADE clause. like below. When the defs are changed, do the DROPs and (re)CREATEs and try the DELETE(s) again.

ALTER TABLE [dbo].[tblReturnDocument] WITH CHECK ADD 
    CONSTRAINT [FK_tblReturnDocument_tblReturnDocumentBatches] 
    FOREIGN KEY ( [ReturnDocumentBatchID] ) 
    REFERENCES [dbo] . [tblReturnDocumentBatches] ( [ReturnDocumentBatchID] ) 
    ON DELETE CASCADE --<<--
1 Like
5

That delete cascade will work but can be a nightmare as it will lock all tables involved. Deletes will be slow. Your first delete was trying to delete from tblReturnDocumentBatches then the foreign key issue was with tblReturnDocument. You need to delete from tblReturnDocument first, then from tblReturnDocumentBatches. Also, look at the actual foreign keys for tblReturnDocumentBatches. It may have a self referencing FK. For example, some sort of hierarchy where a child id is referencing the parent id in the same table. You would have to delete where ChildId is not null first, then where ChildID is null second.

1 Like
6

Thanks @mike01 I want to avoid Delete Cascade as I don't fully understand it.

These are all the FKs for the 3 related tables:
ALTER TABLE [dbo].[tblReturnDocumentSelections] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocumentSelections_tblReturnDocument] FOREIGN KEY([ReturnDocumentID])
REFERENCES [dbo].[tblReturnDocument] ([ReturnDocumentID])

ALTER TABLE [dbo].[tblReturnDocumentSelections] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocumentSelections_tblEstimate] FOREIGN KEY([EstimateID])
REFERENCES [dbo].[tblEstimate] ([EstimateID])

…..
ALTER TABLE [dbo].[tblReturnDocument] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocument_tblReturnDocumentBatches] FOREIGN KEY([ReturnDocumentBatchID])
REFERENCES [dbo].[tblReturnDocumentBatches] ([ReturnDocumentBatchID])
GO

ALTER TABLE [dbo].[tblReturnDocument] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocument_tblQuote] FOREIGN KEY([QuoteID])
REFERENCES [dbo].[tblQuote] ([QuoteID])

ALTER TABLE [dbo].[tblReturnDocument] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocument_tblDODSubmittalStatus] FOREIGN KEY([DODSubmittalStatusID])
REFERENCES [dbo].[tblDODSubmittalStatus] ([DODSubmittalStatusID])
………

This is the table the Delete fails on:

ALTER TABLE [dbo].[tblReturnDocumentBatches] WITH CHECK ADD CONSTRAINT [FK_tblReturnDocumentBatches_tblQuote] FOREIGN KEY([QuoteID])
REFERENCES [dbo].[tblQuote] ([QuoteID])

The TSQL:
Declare @CompareDate char(10)
Set @CompareDate = (Select CompareDate from OzTest.dbo.OzTestCompareDate);
DELETE FROM Batches
FROM dbo.tblReturnDocumentBatches Batches

INNER JOIN dbo.tblQuote Q
ON Q.QuoteID = Batches.QuoteID
WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

The DELETE statement conflicted with the REFERENCE constraint "FK_tblReturnDocument_tblReturnDocumentBatches". The conflict occurred in database "OzTest", table "dbo.tblReturnDocument", column 'ReturnDocumentBatchID'.

I have tried running the deletes in various orders, - but this table always fails with the same FK error.

I'm not sure what my next steps are. Thanks