DELETE statement contrary to REFERENCE

Question

I have a table_Project with CustomerID (associated with tbl_Customer). In tbl_Customer, I have Customer_ID (as a key) and other information such as Phone, Email, etc.

To remove it from Gridview, I use this DeleteCommand:

DeleteCommand="DELETE FROM [tbl_Customer] WHERE [Customer_ID] = @Customer_ID" 

But this 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 the CustomerInfo update, I get no errors. I have seen various solutions for C #, but I am using .net

Any ideas?

Answer 1

Your problem has something to do with referential integrity. You cannot delete a record (from the tbl_Customer table) that is currently referenced by some records in a specific table (for example, table_Project ). UPDATE is different from DELETE if you do not update the key, in your case CustomerID . UPDATE changes the record, and DELETE deletes 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 whatever you want on record 2 the tbl_Customer table, because there is no record referencing it. But in record 1 you cannot delete it if two records from table_Project not deleted. You can change the CustomerName column, but not the CustomerID , if still referenced.

Answer 2

You cannot delete the client if the project refers to it .. What does the error mean ..

I do not understand your

I saw different solutions for C #, but I use .net

But you have different solutions:

• Change the client referenced by all projects related to the old one (the one you want to delete)
• Quotation limit (not a good option)
• Delete the project before uninstalling the client (manually or using the cascade deletion)

Answer 3

Another thing you can do is change the SQL link to the SQL server so that cascades when you delete it. This will delete the entries down. Therefore, if you delete a record in table a, any record in table b that refers to this record will also be deleted.