Tech Junkie Blog: The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'.

Friday, July 25, 2014

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'.

The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'. The statement has been terminated. Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. Exception Details: System.Data.SqlClient.SqlException: The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'. The statement has been terminated. Source Error: An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below. Stack Trace: [SqlException (0x80131904): The DELETE statement conflicted with the REFERENCE constraint "FK_Order_Details_Products". The conflict occurred in database "Northwind", table "dbo.Order Details", column 'ProductID'. The statement has been terminated.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +1789294 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction) +5340642 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, Boolean callerHasConnectionLock, Boolean asyncClose) +244 System.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady) +1691 System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) +275 System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async, Int32 timeout, Task& task, Boolean asyncWrite, SqlDataReader ds) +1421 System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean asyncWrite) +177 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(TaskCompletionSource`1 completion, String methodName, Boolean sendToPipe, Int32 timeout, Boolean asyncWrite) +208 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +163 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDbCommand(DbCommand command, DataSourceOperation operation) +378 System.Web.UI.WebControls.SqlDataSourceView.ExecuteDelete(IDictionary keys, IDictionary oldValues) +568 System.Web.UI.DataSourceView.Delete(IDictionary keys, IDictionary oldValues, DataSourceViewOperationCallback callback) +84 System.Web.UI.WebControls.GridView.HandleDelete(GridViewRow row, Int32 rowIndex) +930 System.Web.UI.WebControls.GridView.HandleEvent(EventArgs e, Boolean causesValidation, String validationGroup) +974 System.Web.UI.WebControls.GridView.RaisePostBackEvent(String eventArgument) +201 System.Web.UI.WebControls.GridView.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +9703566 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1724 Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.34009



This error occurs when you tried to use the default "DeleteStatement" generated by SQL Builder in Visual Studio 2013 for the "Products" table in the "Northwind" database.  What it says basically is that the query is trying to delete a product that is being reference by another table call the "Order Details" table.  The solution to the problem is to delete the child records in the "Order Details" table before you delete the record in the "Products" table.
In this example the scenario is that we added a SqlDataSource control to our .aspx page and we used the auto-generated DELETE statements generated by Visual Studio. We click on the "Delete" link on our GridView and received the above error.
GridView delete link

If you select the following checkboxes, you will get auto generated SQL statements for INSERT, UPDATE, and DELETE.  The problem is the DELETE statement that were generated by Visual Studio only deletes the record in the Products table.

Generate INSERT, UPDATE, and DELETE statements


This is the code that is being generated:

DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" 

If you look at the Order Details table in SQL Server Studio you will see that the ProductID field in the Order Details table is referencing the ProductID in the Products table

Foreign Key Relationships


The easiest way to fix this issue is to modify the delete statement that was generated by Visual Studio.  What you want to do is delete the records from the lowest child level.  Since the "Order Details"  table is referencing the "Products" table we want to delete all the records associated with the record in the "Order Details" table first.

So here is the original DELETE statement


DeleteCommand="DELETE FROM [Products] WHERE [ProductID] = @ProductID" 


We want to change it to
DeleteCommand="DELETE FROM [Order Details] WHERE [ProductID] = @ProductID; DELETE FROM [Products] WHERE [ProductID] = @ProductID"

Now you can delete the "Products" record with no issue using the SqlDataSource control and a GridView control.  There are more elegant ways of resolving this issue, however since this is a blog, we will implement the sitcom 30 minutes solution.  The main purpose of this blog is to make the point that the reason the error occur is because there's a foreign key constraint violation.  Which is a good thing, not all exceptions are a bad thing.  You just have to handle it.


No comments:

Post a Comment