The DELETE statement conflicted with the REFERENCE

Question

I have a table_Project with a CustomerID in it (linked to tbl_Customer). In tbl_Customer I have Customer_ID(as key) and some other info like Phone, Email etc. To delete it from the Gridview I use this DeleteCommand: ``` DeleteCommand="DELETE FROM [tbl_Customer] WHERE [Customer_ID] = @Customer_ID" ``` But it gives me the following error: `The DELETE statement conflicted with the REFERENCE constraint "Klant_Relatie". The conflict occurred in database "Database_1", table "dbo.tbl_Project", column 'CustomerID'. The statement has been terminated.` But with updating the CustomerInfo I do not get any error. I have seen different solutions for C# but I use .net Any idea's?

Accepted Answer

Your problem has something to do with referential integrity. You cannot delete a record (from table `tbl_Customer`) which is currently being referenced by some records in a certain table (table `table_Project` for example). `UPDATE` is different from `DELETE` unless you are updating the key, in your case `CustomerID`. `UPDATE` modifies a record while `DELETE` removes the record.

Take a look at this example,

tbl_Customer table

```
CustomerID  CustomerName
1           Hello
2           World
```

table_Project table

```
CustomerID  ProjectName
1           Webscripting
1           Database Maintenance
```

You can always do anything you want on record `2` of table `tbl_customer` because there is no record that is referencing to it. But in record `1`, you can't delete it unless the two records from table `table_Project` are deleted. You can modify column `CustomerName` but not `CustomerID` is if still being referenced.

The DELETE statement conflicted with the REFERENCE

Solution

Problem