I got error The DELETE statement conflicted with the REFERENCE constraint

Question

I tried to truncate a table with foreign keys and got the message       Cannot truncate table  because it is being referenced by a FOREIGN KEY constraint      I read a lot of literature about the problem and thought that I found the solution by using delete   DELETE FROM table name DBCC CHECKIDENT  table name  RESEED  0    But I still got an error message       The DELETE statement conflicted with the REFERENCE constraint      When I try to delete with Microsoft Management Studio and execute the previous query   DELETE FROM table name DBCC CHECKIDENT  table name  RESEED  0    it doesn t give an error and works properly  I want to delete all information from a table and add new into it  but I don t want to drop and create foreign keys

User · Answer

You are trying to delete a row that is referenced by another row (possibly in another table).

You need to delete that row first (or at least re-set its foreign key to something else), otherwise you’d end up with a row that references a non-existing row. The database forbids that.

User · Answer

The error means that you have data in other tables that references the data you are trying to delete   You would need to either drop and recreate the constraints or delete the data that the Foreign Key references   Suppose you have the following tables  dbo Students   StudentId StudentName StudentTypeId     dbo StudentTypes   StudentTypeId StudentType     Suppose a Foreign Key constraint exists between the StudentTypeId column in StudentTypes and the StudentTypeId column in Students  If you try to delete all the data in StudentTypes an error will occur as the StudentTypeId column in Students reference the data in the StudentTypes table   EDIT   DELETE and TRUNCATE essentially do the same thing   The only difference is that TRUNCATE does not save the changes in to the Log file   Also you can t use a WHERE clause with TRUNCATE  AS to why you can run this in SSMS but not via your Application   I really can t see this happening   The FK constraint would still throw an error regardless of where the transaction originated from

User · Answer

Have you considered applying ON DELETE CASCADE where relevant

User · Answer

To DELETE  without changing the references  you should first delete or otherwise alter  in a manner suitable for your purposes  all relevant rows in other tables   To TRUNCATE you must remove the references  TRUNCATE is a DDL statement  comparable to CREATE and DROP  not a DML statement  like INSERT and DELETE  and doesn t cause triggers  whether explicit or those associated with references and other constraints  to be fired  Because of this  the database could be put into an inconsistent state if TRUNCATE was allowed on tables with references  This was a rule when TRUNCATE was an extension to the standard used by some systems  and is mandated by the the standard  now that it has been added

[c#] I got error "The DELETE statement conflicted with the REFERENCE constraint"

Examples related to c#

Examples related to sql

Examples related to truncate

Examples related to sql-delete