Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

use the following command after deletion of all rows in that table by using delete statement

delete from tablename

DBCC CHECKIDENT ('tablename', RESEED, 0)

EDIT: Corrected syntax for SQL Server

I have just found that you can use TRUNCATE table on a parent table with foreign key constraints on a child as long as you DISABLE the constraints on the **child** table first.
E.g.

Foreign key CONSTRAINT child_par_ref on child table, references PARENT_TABLE

ALTER TABLE CHILD_TABLE DISABLE CONSTRAINT child_par_ref;
TRUNCATE TABLE CHILD_TABLE;
TRUNCATE TABLE PARENT_TABLE;
ALTER TABLE CHILD_TABLE ENABLE CONSTRAINT child_par_ref;

you can follow this step,
By `reseeding table` you can delete the data of the table.

delete from table_name
dbcc checkident('table_name',reseed,0)

if some error comes then you have to reseed the primary table.

Well, since I did not find *examples* of the **very simple** solution I used, which is:

1. Drop foreign key;
2. Truncate table
3. Recreate foreign key

Here it goes:

1) Find the foreign key name that is causing the failure (for example: FK_PROBLEM_REASON, with field `ID`, from table `TABLE_OWNING_CONSTRAINT`)
2) Remove that key from the table:

ALTER TABLE TABLE_OWNING_CONSTRAINT DROP CONSTRAINT FK_PROBLEM_REASON

3) Truncate wanted table

TRUNCATE TABLE TABLE_TO_TRUNCATE

4) Re-add the key to that first table:

ALTER TABLE TABLE_OWNING_CONSTRAINT ADD CONSTRAINT FK_PROBLEM_REASON FOREIGN KEY(ID) REFERENCES TABLE_TO_TRUNCATE (ID)

That's it.

If none of these answers worked like in my case do this:

1. Drop constraints
2. Set all values to allow nulls
3. Truncate table
4. Add constraints that were dropped.

Good luck!

Because `TRUNCATE TABLE` is a [DDL command][1], it cannot check to see whether the records in the table are being referenced by a record in the child table.

This is why `DELETE` works and `TRUNCATE TABLE` doesn't: because the database is able to make sure that it isn't being referenced by another record.

[1]:http://en.wikipedia.org/wiki/Data_definition_language "Data definition language"

If I understand correctly, what you *want* to do is to have a clean environment to be set up for DB involving integration tests.

My approach here would be to drop the whole schema and recreate it later.

### Reasons:

1. You probably already have a "create schema" script. Re-using it for test isolation is easy.
2. Creating a schema is pretty quick.
3. With that approach, it is pretty easy to set up your script to have each fixture create a NEW schema (with a temporary name), and then you can start running test-fixtures in parallel, making the slowest part of your test suite much faster.

In SSMS I had Diagram open showing the Key. After deleting the Key and truncating the file I refreshed then focused back on the Diagram and created an update by clearing then restoring an Identity box. Saving the Diagram brought up a Save dialog box, than a "Changes were made in the database while you where working" dialog box, clicking Yes restored the Key, restoring it from the latched copy in the Diagram.

The easiest way:<br>
1 - Enter in phpmyadmin<br>
2 - Click on table name in left column<br>
3 - Click in Operation (top menu)<br>
4 - Click "Empty the table (TRUNCATE)<br>
5 - Disable box "Enable foreign key checks"<br>
6 - Done!

Link to image tutorial<br>
[Tutorial: ][1]http://www.imageno.com/wz6gv1wuqajrpic.html
<br>
(sorry, I don't have enough reputation to upload images here :P)


[1]:

The solution @denver_citizen provided above did not work for me, but I liked the spirit of it so I modified a few things :

- made it a stored procedure
- changed the way the foreign keys are populated and recreated
- the original script truncates all referenced tables, this can cause foreign key violation error when the referenced table has other foreign key references. This script truncates only the table specified as parameter. It is up to the user, to call this stored procedure multiple times on all tables in the correct order

For the benefit of the public here is the updated script :

CREATE PROCEDURE [dbo].[truncate_non_empty_table]

@TableToTruncate VARCHAR(64)

AS

BEGIN

SET NOCOUNT ON

-- GLOBAL VARIABLES
DECLARE @i int
DECLARE @Debug bit
DECLARE @Recycle bit
DECLARE @Verbose bit
DECLARE @TableName varchar(80)
DECLARE @ColumnName varchar(80)
DECLARE @ReferencedTableName varchar(80)
DECLARE @ReferencedColumnName varchar(80)
DECLARE @ConstraintName varchar(250)

DECLARE @CreateStatement varchar(max)
DECLARE @DropStatement varchar(max)
DECLARE @TruncateStatement varchar(max)
DECLARE @CreateStatementTemp varchar(max)
DECLARE @DropStatementTemp varchar(max)
DECLARE @TruncateStatementTemp varchar(max)
DECLARE @Statement varchar(max)

-- 1 = Will not execute statements
SET @Debug = 0
-- 0 = Will not create or truncate storage table
-- 1 = Will create or truncate storage table
SET @Recycle = 0
-- 1 = Will print a message on every step
set @Verbose = 1

SET @i = 1
SET @CreateStatement = 'ALTER TABLE [dbo].[<tablename>] WITH NOCHECK ADD CONSTRAINT [<constraintname>] FOREIGN KEY([<column>]) REFERENCES [dbo].[<reftable>] ([<refcolumn>])'
SET @DropStatement = 'ALTER TABLE [dbo].[<tablename>] DROP CONSTRAINT [<constraintname>]'
SET @TruncateStatement = 'TRUNCATE TABLE [<tablename>]'

-- Drop Temporary tables

IF OBJECT_ID('tempdb..#FKs') IS NOT NULL
DROP TABLE #FKs

-- GET FKs
SELECT ROW_NUMBER() OVER (ORDER BY OBJECT_NAME(parent_object_id), clm1.name) as ID,
OBJECT_NAME(constraint_object_id) as ConstraintName,
OBJECT_NAME(parent_object_id) as TableName,
clm1.name as ColumnName,
OBJECT_NAME(referenced_object_id) as ReferencedTableName,
clm2.name as ReferencedColumnName
INTO #FKs
FROM sys.foreign_key_columns fk
JOIN sys.columns clm1
ON fk.parent_column_id = clm1.column_id
AND fk.parent_object_id = clm1.object_id
JOIN sys.columns clm2
ON fk.referenced_column_id = clm2.column_id
AND fk.referenced_object_id= clm2.object_id
--WHERE OBJECT_NAME(parent_object_id) not in ('//tables that you do not wont to be truncated')
WHERE OBJECT_NAME(referenced_object_id) = @TableToTruncate
ORDER BY OBJECT_NAME(parent_object_id)


-- Prepare Storage Table
IF Not EXISTS(SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Internal_FK_Definition_Storage')
BEGIN
IF @Verbose = 1
PRINT '1. Creating Process Specific Tables...'

-- CREATE STORAGE TABLE IF IT DOES NOT EXISTS
CREATE TABLE [Internal_FK_Definition_Storage]
(
ID int not null identity(1,1) primary key,
FK_Name varchar(250) not null,
FK_CreationStatement varchar(max) not null,
FK_DestructionStatement varchar(max) not null,
Table_TruncationStatement varchar(max) not null
)
END
ELSE
BEGIN
IF @Recycle = 0
BEGIN
IF @Verbose = 1
PRINT '1. Truncating Process Specific Tables...'

-- TRUNCATE TABLE IF IT ALREADY EXISTS
TRUNCATE TABLE [Internal_FK_Definition_Storage]
END
ELSE
PRINT '1. Process specific table will be recycled from previous execution...'
END


IF @Recycle = 0
BEGIN

IF @Verbose = 1
PRINT '2. Backing up Foreign Key Definitions...'

-- Fetch and persist FKs
WHILE (@i <= (SELECT MAX(ID) FROM #FKs))
BEGIN
SET @ConstraintName = (SELECT ConstraintName FROM #FKs WHERE ID = @i)
SET @TableName = (SELECT TableName FROM #FKs WHERE ID = @i)
SET @ColumnName = (SELECT ColumnName FROM #FKs WHERE ID = @i)
SET @ReferencedTableName = (SELECT ReferencedTableName FROM #FKs WHERE ID = @i)
SET @ReferencedColumnName = (SELECT ReferencedColumnName FROM #FKs WHERE ID = @i)

SET @DropStatementTemp = REPLACE(REPLACE(@DropStatement,'<tablename>',@TableName),'<constraintname>',@ConstraintName)
SET @CreateStatementTemp = REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(@CreateStatement,'<tablename>',@TableName),'<column>',@ColumnName),'<constraintname>',@ConstraintName),'<reftable>',@ReferencedTableName),'<refcolumn>',@ReferencedColumnName)
SET @TruncateStatementTemp = REPLACE(@TruncateStatement,'<tablename>',@TableName)

INSERT INTO [Internal_FK_Definition_Storage]
SELECT @ConstraintName, @CreateStatementTemp, @DropStatementTemp, @TruncateStatementTemp

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > Backing up [' + @ConstraintName + '] from [' + @TableName + ']'

END
END
ELSE
PRINT '2. Backup up was recycled from previous execution...'

IF @Verbose = 1
PRINT '3. Dropping Foreign Keys...'

-- DROP FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_DestructionStatement FROM [Internal_FK_Definition_Storage] WITH (NOLOCK) WHERE ID = @i)

IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)

SET @i = @i + 1


IF @Verbose = 1
PRINT ' > Dropping [' + @ConstraintName + ']'

END


IF @Verbose = 1
PRINT '4. Truncating Tables...'

-- TRUNCATE TABLES
-- SzP: commented out as the tables to be truncated might also contain tables that has foreign keys
-- to resolve this the stored procedure should be called recursively, but I dont have the time to do it...
/*
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN

SET @Statement = (SELECT Table_TruncationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)

SET @i = @i + 1

IF @Verbose = 1
PRINT ' > ' + @Statement
END
*/


IF @Verbose = 1
PRINT ' > TRUNCATE TABLE [' + @TableToTruncate + ']'

IF @Debug = 1
PRINT 'TRUNCATE TABLE [' + @TableToTruncate + ']'
ELSE
EXEC('TRUNCATE TABLE [' + @TableToTruncate + ']')


IF @Verbose = 1
PRINT '5. Re-creating Foreign Keys...'

-- CREATE FOREING KEYS
SET @i = 1
WHILE (@i <= (SELECT MAX(ID) FROM [Internal_FK_Definition_Storage]))
BEGIN
SET @ConstraintName = (SELECT FK_Name FROM [Internal_FK_Definition_Storage] WHERE ID = @i)
SET @Statement = (SELECT FK_CreationStatement FROM [Internal_FK_Definition_Storage] WHERE ID = @i)

IF @Debug = 1
PRINT @Statement
ELSE
EXEC(@Statement)

SET @i = @i + 1


IF @Verbose = 1
PRINT ' > Re-creating [' + @ConstraintName + ']'

END

IF @Verbose = 1
PRINT '6. Process Completed'


END