I got error "The DELETE statement conflicted with the REFERENCE constraint"

Question

Hello, I have a big problem :) I try to truncate table with foreign keys and I got the message "Cannot truncate table because it is being referenced by a FOREIGN KEY constraint". I read a lot literature about the problem and thought that I found the solution by using delete (e.g. DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0) ). By still got error message "The DELETE statement conflicted with the REFERENCE constraint". When I am trying to delete with microsoft management studio and execute the previous query (e.g. DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0) ) it didn't give an error it worked 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 :).

Thanks in advance

Answer 1

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.

Answer 2

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.

Answer 3

It Simply shows CASCADING effect

CASCADING is say you are trying to delete a record with Primary Key = 1 and this Primary Key is used in some other table as a Reference key taht menas Primary key =1 is a Parent row and if you delete this parent row it's childern will be orphaned let's take a real world scenarion

say you are creating a online Quiz application and you design your database in a manner that each questions answer/s are stored in two table tblQuestion and tblAnswers and each row in tblAnswers stores a value that shows for which question this answer represents. so try to think if you delete a question in tblQuestion table then all answers in tblAnswers table will not be able to identify which question they represent as an answers.

Answer 4

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.

Answer 5

Have you considered applying ON DELETE CASCADE where relevant?