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.

Answer 1

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.