Hi I have a complex db with many tables and relations what we want to do is to copy all tables from a database to another but only taking rows starting from one table (salesorder) and going down following relations. So I avoided the .Include, and use IQueryable queries (one for each table) there is no problem with the insert
the problem is when I want to delete all rows from all tables I got a constraint violation I use .RemoveRange(tableRowsqueryable)
I have already tried to disable all FK check, but when I do reenable I get the same error
the error
SqlException: The DELETE statement conflicted with the REFERENCE constraint “FK_InvParty_Stop_InvParty”. The conflict occurred in database “UDM”, table “dbo.InvParty_Stop”, column ‘InvPartyID’.
the InvPartyID column is also in the PK of InvParty_Stop table
I have tried debugging to check if there is something missing, but I don’t see nothing wrong! The other solution is to use a stored procedure to do this job
Advertisement
Answer
When an item with a foreign key relation is deleted or updated you need to specify what you need to do with related records. These settings are performed at the DB level. You can check the syntax reference on MSDN. You have these options:
[ ON DELETE { NO ACTION | CASCADE | SET NULL | SET DEFAULT } ]
- NO ACTION – The default behavior when you have an error
- CASCADE – Delete related records. When all related records are deleted implicitly.
- SET NULL and SET DEFAULT – Update Reference values with NULL or a default value
You can check the detailed description in the following help article and choose that best approach in your scenario.