. Advertisement .
..3..
. Advertisement .
..4..
I am new to csharp and searching the “the delete statement conflicted with the reference constraint” to understand it better. It seems it doesn’t work as expected when I used some suggestions before. Here is the command line I use:
DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)
DELETE FROM table_name DBCC CHECKIDENT (table_name, RESEED, 0)
The error I’m getting is below:
"Cannot truncate table because it is being referenced by a FOREIGN KEY constraint".
"The DELETE statement conflicted with the REFERENCE constraint".
Please give me the solution to this issue.
The cause:
TRUNCATE
andDELETE
are practically the same thing.TRUNCATE
differs simply in that it does not store changes to the Log file.TRUNCATE
also doesn’t allow you to utilize aWHERE
clause.This can be done in SSMS, but not through your application. Regardless of where the transaction started, the FK restriction would raise an error.
Solution:
To
DELETE
without affecting the references, you must first delete or otherwise alter the relevant rows in other tables (in a way suited for your objectives).You must remove the references in order to
TRUNCATE
.TRUNCATE
is aDDL
statement (like toCREATE
andDROP
) rather than a DML statement (likeINSERT
andDELETE
), and it does not trigger any triggers, whether explicit or implicit, connected with references and other restrictions. As a result, ifTRUNCATE
was allowed on tables with references, the database may become inconsistent. This was a rule whenTRUNCATE
was an optional addition to the standard utilized by some systems, and it is now required by the standard.This error indicates that data in other tables refers to the data that you are trying delete.
It would be necessary to either delete or recreate the Foreign Key references.
Let’s say you have these tables
Let’s say there is a Foreign Key constraint between the
StudentTypeId
andStudentTypeId
columns inStudentTypes
.You will get an error if you attempt to delete all data in
StudentTypes
, as theStudentTypeId
column ofStudents
refers to the data in theStudentTypes
table.EDIT:
DELETE
andTRUNCATE
do the exact same thing.TRUNCATE
saves the changes to the Log file, butDELETE
does not. You can’t also useWHERE
clauses withTRUNCATE
As to why this can be run in SSMS and not through your Application. This is not something I can see happening. The FK constraint will still throw an error, regardless of origin.