By Peter


2010-09-23 07:42:08 8 Comments

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.

4 comments

@annakata 2010-09-23 09:56:26

Have you considered applying ON DELETE CASCADE where relevant?

@Jon Hanna 2010-09-23 09:51:01

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.

@codingbadger 2010-09-23 07:50:46

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.

@Peter 2010-09-23 07:56:11

Thanks, I will try to delete the "child" table first and after it the master. Is it going to work if I use TRUNCATE instead of DELETE. Can you tell me why in MS management studio I can delete the rows from the table with the same query which is giving me error when I try it with application

@codingbadger 2010-09-23 08:05:14

@Peter - I have updated my answer

@Jon Hanna 2010-09-23 11:12:21

It's far from the "only difference", e.g. truncate can't happen if there are any references at all. And truncate can't happen in a transaction on most systems. And truncate can't happen as part of a stored procedure. And truncate must be in a separate batch in most systems. And truncate can't fire triggers.

@codingbadger 2010-09-23 11:27:51

@Jon Hanna - Of course, you are correct. I should have really detailed the differences. Maybe wording it "only difference" is misleading.

@Mike 2017-04-26 12:59:10

In SQL Server you can Truncate in a stored procedure and/ or as part of a transaction. At least in SSMS 2012 and higher.

@Konrad Rudolph 2010-09-23 07:46:24

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.

@Peter 2010-09-23 10:57:35

I resolved my problem with delete every row from "child" table and after it delete all rows from "parent" table. But still have some questions :) like "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."

Related Questions

Sponsored Content

14 Answered Questions

[SOLVED] The ALTER TABLE statement conflicted with the FOREIGN KEY constraint

27 Answered Questions

13 Answered Questions

9 Answered Questions

[SOLVED] How to add 'ON DELETE CASCADE' in ALTER TABLE statement

  • 2009-10-15 10:44:59
  • Ula Krukar
  • 176426 View
  • 97 Score
  • 9 Answer
  • Tags:   sql oracle

22 Answered Questions

[SOLVED] How can I list all foreign keys referencing a given table in SQL Server?

  • 2009-01-27 12:17:59
  • chillitom
  • 539680 View
  • 648 Score
  • 22 Answer
  • Tags:   sql sql-server tsql

11 Answered Questions

[SOLVED] How to truncate a foreign key constrained table?

13 Answered Questions

[SOLVED] How to drop SQL default constraint without knowing its name?

  • 2009-09-16 01:20:54
  • Robo
  • 150800 View
  • 178 Score
  • 13 Answer
  • Tags:   sql sql-server tsql

1 Answered Questions

The DELETE statement conflicted with the REFERENCE constraint, cascading delete

  • 2018-04-25 08:53:22
  • user2983359
  • 473 View
  • 0 Score
  • 1 Answer
  • Tags:   sql sql-server

14 Answered Questions

1 Answered Questions

[SOLVED] The DELETE statement conflict REFERENCE constraint

  • 2016-11-29 12:51:41
  • user1673665
  • 7476 View
  • 2 Score
  • 1 Answer
  • Tags:   sql sql-server

Sponsored Content