Insert Stmt Fails With Foreign Key Conflict Error

SQL Server Administration
1

Hi experts,

BEGIN TRANSACTION;

INSERT OzTrainHydro.dbo.tblRelease
SELECT
x.[EstimateID],
x.[estContractPrice]

From MyServer.Oz_Dev.dbo.tblRelease x

   JOIN MyServer.Oz_Dev.dbo.tblEstimate e on x.EstimateID = e.EstimateID 
   JOIN MyServer.Oz_Dev.dbo.tblQuote q ON e.QuoteID = q.QuoteID

where q.BrId = 1012 and q.quoStatus In ('Active', 'Hold')

And Not Exists (Select * From OzTrainHydro.dbo.tblRelease t Where t.EstimateID = x.EstimateID)
And Not Exists (Select * From OzTrainHydro.dbo.tblEstimate est Where est.EstimateID = x.EstimateID)
COMMIT TRANSACTION;

The INSERT statement conflicted with the FOREIGN KEY constraint "FK_tblRelease_tblEstimate". The conflict occurred in database "OzTrainHydro", table "dbo.tblEstimate", column 'EstimateID'

This is the foreign key FK_tblRelease_tblEstimate:

ALTER TABLE [dbo].[tblRelease] WITH NOCHECK ADD CONSTRAINT [FK_tblRelease_tblEstimate] FOREIGN KEY([EstimateID])
REFERENCES [dbo].[tblEstimate] ([EstimateID])
GO

ALTER TABLE [dbo].[tblRelease] CHECK CONSTRAINT [FK_tblRelease_tblEstimate]

** My question is how can I determine WHICH row, in which table is causing the failure?
Thanks.

2

First thing - you don't need (and shouldn't) query the destination table for existing estimate ID's. The goal should be to insert into the table those items that do not already exist in that table.

Second thing - make sure the query you build does not have any duplicates itself.

Start with the query to be inserted:

Select e.EstimateID
     , e.estContractPrice
  From dbo.tblEstimate            e
 Inner Join dbo.tblQuote          q On q.QuoteID = e.QuoteID
 Where q.BrId = 1012 
   And q.quoStatus In ('Active', 'Hold')
   And Not Exists (Select * From dbo.tblRelease r Where r.EstimateID = e.EstimateID);

Review that query and adjust as needed - this should return the list of ID's that do not exist in the release table. Once you have this working, then convert to an insert statement.

The update statement will use a join from the release table - this is needed because that is the table being updated.

--Update r
--   Set ...
Select *
  From dbo.tblRelease           r
 Inner Join dbo.tblEstimate     e On e.EstimateID = r.EstimateID
 Inner Join dbo.tblQuote        q On q.QuoteID = e.QuoteID
 Where q.BrId = 1012 
   And q.quoStatus In ('Active', 'Hold');

Again - validate the select and once validated you can comment out the select and uncomment the set. The key here is to validate that you are getting the appropriate values from the query to update the values in the release table.

1 Like
3

Thanks jeffw8713. I'm just now reading your post but I was able to insert the rows, although I took a different route. Thanks for your tip.

4

If you came up with a different solution - it would help others to see how you solved the problem.

3 Likes