Another Foreign Key Error

1

Hi experts,
I have yet another FK error issue when I try to delete.

The DELETE statement conflicted with the REFERENCE constraint "FK_tblRFISummary_tblRFIQuestion_tblID". The conflict occurred in table "dbo.tblRFIQuestions", column 'tblID'.

tblRFIQuestions has a FK on tblID in tblRFISummary:
ALTER TABLE [dbo].[tblRFIQuestions] WITH CHECK ADD CONSTRAINT [FK_tblRFISummary_tblRFIQuestion_tblID] FOREIGN KEY([tblID])
REFERENCES [dbo].[tblRFISummary] ([tblID])
GO

ALTER TABLE [dbo].[tblRFIQuestions] CHECK CONSTRAINT [FK_tblRFISummary_tblRFIQuestion_tblID]

Interestingly, I get the error on tblRFISummary whether I delete it before or after deleting from tblRFI Questions.

Any tips will be greatly appreciated. (No I haven't tried the CASCADE option)

2

to make it simple, these are the steps??

  1. Delete from tblRFIQuestions where TLBID = @TBLDID
  2. returns FK constraint error?
  3. What does this do??
    a) Delete from tblRFISummary where TLBID = @TBLDID
    b) Delete from tblRFIQuestions where TLBID = @TBLDID
1 Like
3

Mike01, I should have provided my complete set of code.

SET QUOTED_IDENTIFIER ON;
Declare @CompareDate char(10)
Set @CompareDate = (Select CompareDate from OzTestCompareDate);
DELETE FROM RFIQuestions
FROM tblRFIQuestions RFIQuestions
INNER JOIN tblEstimate estimate ON Estimate.EstimateID = RFIQuestions.EstimateID
INNER JOIN tblQuote Q ON Q.QuoteID = Estimate.QuoteID
WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

94,379 deleted successfully

SET QUOTED_IDENTIFIER ON;
Declare @CompareDate char(10)
Set @CompareDate = (Select CompareDate from OzTestCompareDate);
DELETE FROM RFISummary
FROM tblRFISummary RFISummary
INNER JOIN tblQuote Q
ON Q.QuoteID = RFISummary.QuoteID
WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

** fails with The DELETE statement conflicted with the REFERENCE constraint "FK_tblRFISummary_tblRFIQuestion_tblID". The conflict occurred in table "dbo.tblRFIQuestions", column 'tblID'

NOTE: I have discovered that 81 tblIDs in tblRFISummary do NOT exist in tblRFIQuestions.
So I suppose I have corrupted data. Not sure how that could happen unless the FK is corrupted.
What are your thoughts? Thanks

4

the second delete needs to join to tblEstimate as well. There are records in the estimate table that aren't in the tblQuote table that's causing it.

for instance,
tblRFIQuestions has tblid 1, 2, 3
tblEstimate has tblid 1,2
tblQuote has tblid 1, 2, 3
RFISummary has tblid 1, 2, 3

the first delete will only delete tblid 1&2, leaving 3
the second is trying to delete all 3

1 Like
5

Note that you also have the option of SET NULL rather than just CASCADE. CASCADE can be quite scary in some setups.

1 Like
6

mike01, I like your idea. This is what I ran:
SET QUOTED_IDENTIFIER ON;
Declare @CompareDate char(10)
Set @CompareDate = (Select CompareDate from OzTestCompareDate);
DELETE FROM RFISummary
FROM tblRFISummary RFISummary
INNER JOIN tblQuote Q ON Q.QuoteID = RFISummary.QuoteID
INNER JOIN tblRFIQuestions Questions RFISummary.tblID Questions.tblID`
WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

Got the same FK error
So I took it one step forther by checking existence of tblID in the other table:

.SET QUOTED_IDENTIFIER ON;
Declare @CompareDate char(10)
Set @CompareDate = (Select CompareDate from OzTestCompareDate);
DELETE FROM RFISummary
FROM tblRFISummary RFISummary
INNER JOIN tblEstimate estimate ON Estimate.EstimateID = RFISummary.EstimateID
INNER JOIN tblQuote Q ON Q.QuoteID = Estimate.QuoteID
INNER JOIN tblRFIQuestions Questions on RFISummary.tblID = Questions.tblID
WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

But that also gives the FK error.

Any other ideas? Thanks

7

the second one fails because you have already deleted the records from tblRFIQuestions. I would try something like this then

drop table if exists #t
go

Create table #t (tblID int)

Set @CompareDate = (Select CompareDate from OzTestCompareDate);
DELETE FROM RFIQuestions
FROM tblRFIQuestions RFIQuestions
INNER JOIN tblEstimate estimate ON Estimate.EstimateID = RFIQuestions.EstimateID
INNER JOIN tblQuote Q ON Q.QuoteID = Estimate.QuoteID
output deleted.tblID into #t
WHERE (Q.quostatus NOT IN ('Active', 'HOLD') AND Q.DownloadDate < @CompareDate);

DELETE FROM RFISummary
FROM tblRFISummary RFISummary
join #t t
on RFISummary.tblID = t.tblID

1 Like
8

Thanks, mike01.