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

Question

Using MSSQL2005, can I truncate a table with a foreign key constraint if I first truncate the child table (the table with the primary key of the FK relationship)?

I know that I can either

• Use a DELETE without a where clause and then RESEED the identity (or)
• Remove the FK, truncate the table, and recreate the FK.

I thought that as long as I truncated the child table before the parent, I'd be okay without doing either of the options above, but I'm getting this error:

Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.

Health

Related post - How to truncate a foreign key constrained table? – Truancy

Answer 1

Correct; you cannot truncate a table which has an FK constraint on it.

Typically my process for this is:

1. Drop the constraints
2. Trunc the table
3. Recreate the constraints.

(All in a transaction, of course.)

Of course, this only applies if the child has already been truncated. Otherwise I go a different route, dependent entirely on what my data looks like. (Too many variables to get into here.)

The original poster determined WHY this is the case; see this answer for more details.

Dilemma

A "DELETE FROM" does not reset auto-incrementing columns. A truncate does. They are not functionally equivalent. – Halfwit

Truncate is often exactly what you want to do if you're deleting huge amounts of data. Truncating a million rows? A billion? 1 ms... so, @M07, please don't say a "delete from approach is cleaner", because that's just not remotely accurate. – Stria

After deleting large data, user have to shrink the tables and log files also to reclaim the disk space. – Michalmichalak

Magic Shrink button (or script) is not advised 99% of the time. – Westering

And how would you do that? Example requests? – Pappy

Constraints here is all foreign key in the table or any table in the database which has foreign key pointing to this table – Swanky

This is MySQL stupidity. I can truncate Tables in SQL Server and probably Oracle with constraints on, providing I truncate the tables in the correct order - from lowest child to first root table. I shouldn't have to drop constraints in order to truncate a table! You have to have special rights in order to truncate anyway. – Shoemaker

To drop and recreate constraints you need special rights. More rights I believe than truncate requires in MySQL. So this makes it harder to do this pro-grammatically, unless you want to give the program the "keys to the kingdom" to do pretty much anything. This goes against the last access privileges rule. – Shoemaker

Isen't it possible to truncate when "On Delete" constraint is set to cascade on the tables containing the foreign keys? This constraint allows deletion of rows in the referenced table, so why would deleting all rows of the table (truncating) be restricted? This does not make any sense to me – Prurigo

@JohnFoll which version? I'm not seeing this behavior – Bewitch

I am not sure now which version I was using, I would have too look it up. I work at a different company now. But I had a locked down Laptop at the time and no administrator rights for the Laptop. I don't know if the security had anything to do with it. – Shoemaker

For those that want to truncate many tables and have to clear out a lot of FK's to do that, there's a handy script over at mssqltips.com/sqlservertip/3347/… to generate the statements for dropping the keys and restoring them once your truncate is complete. – Nudd

Answer 2

DELETE FROM TABLENAME
DBCC CHECKIDENT ('DATABASENAME.dbo.TABLENAME', RESEED, 0)

Note that this isn't probably what you'd want if you have millions+ of records, as it's very slow.

Billat

I wouldn't suggest going this route, as you might also get this error: The DELETE statement conflicted with the REFERENCE constraint – Henricks

Did not work for me. Still getting The DELETE statement conflicted with the REFERENCE constraint. – Contraption

This is straightforward than removing-adding the constraints. I made this a part of my initial setup of master data sql script. – Smew

@Contraption very late to the party but just in case someone else runs into the same problem, that is probably because you still have records in another table referencing the rows that you're trying to delete. If you first remove those rows (or configure cascade delete) then you should no longer get that error and the instructions from this answer should work just fine. – Eichhorn

Worked for me , and if the table has no identity column it will throw an error, but will continue normally – Pisciculture

This solution is the simplest way for at least non-production DBs. – Glisson

@HasanShouman why would you use this for tables without identity column? – Petroleum

Answer 3

Because TRUNCATE TABLE is a DDL command, 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.

Health

Answer 4

Without ALTER TABLE

-- Delete all records
DELETE FROM [TableName]
-- Set current ID to "1"
-- If table already contains data, use "0"
-- If table is empty and never insert data, use "1"
-- Use SP https://github.com/reduardo7/TableTruncate
DBCC CHECKIDENT ([TableName], RESEED, 0)

As Stored Procedure

https://github.com/reduardo7/TableTruncate

Note that this isn't probably what you'd want if you have millions+ of records, as it's very slow.

Correctitude

using reseed new value = 1 after DELETE FROM would start all from ID 2, instead of 1. From Technet (technet.microsoft.com/en-us/library/ms176057%28SQL.90%29.aspx) If no rows have been inserted to the table since it was created, or all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity. Otherwise, the next row inserted uses new_reseed_value + the current increment value. – Cabot

@ZoranP. please see Stored Procedure variant: github.com/reduardo7/TableTruncate – Correctitude

DBCC CHECKIDENT ([TableName], RESEED, 0) not 1 – Dysplasia

@TicoFortes Post updated. Please see Stored Procedure variant – Correctitude

This is not a good approach. As commented by the 700 other version of this same answer to this question. UNLESS your database is in Simple recovery mode, to limit transaction logging. – Immaterial

Answer 5

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

Klug

This answer deserves more votes! In fact I would gladly buy you a beer if I could, Peter :) – Carpal

This was a great help to me today to quickly clear some large tables of their data for testing, Thanks for the quality work on this. – Hypogastrium

Thank you for this piece of code. But watch out, you should add an additional logic to check disabled FKs. Otherwise, you'll enable currently disabled constraints. – Helbon

I made a version with the suggestions by @AndreFigueiredo. I'm putting it on Gitlab: gitlab.com/ranolfi/truncate-referenced-table. Feel free to incorporate the code to your answer. – Mescaline

This is great, but do note that it won't work if your tables aren't in the default (dbo) schema. – Studhorse

How do I even use this? I see it created a procedure apparently? What do I do with it? and how? – Pappy

You can call EXEC dbo.truncate_non_empty_table table_name where table_name is the name of the table to truncate – Klug

Edited for my purposes to account for composite keys, multiple schemas, and cascading deletes. https://mcmap.net/q/64336/-cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint – Claraclarabella

Answer 6

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.

Tomfoolery

This doesn't work if you have multiple tables with foreign key references. You would have to remove a lot of foreign key restraints throughout the entire database. – Cohen

I am getting Msg 3728, Level 16, State 1, Line 1 'FK_User_UserTypeID' is not a constraint. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. See previous errors. – Pied

Answer 7

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

Nonnah

TRUNCATE avoids the log and is considerably faster than DELETE for large tables. As such, this isn't a true equivalent solution. – Shem

How is this answer different from that one, which was given one year before? – Sodden

Answer 8

The process is removing foreign key constraint and truncate table then add constrain by following steps.

This is Just for MySQL

SET FOREIGN_KEY_CHECKS = 0; 

truncate table "yourTableName";

SET FOREIGN_KEY_CHECKS = 1;

Sweptwing

This question is about MS SQL Server, which doesn't have a FOREIGN_KEY_CHECKS setting – Unsocial

I think this would work from MySQL, but not MS SQL Server – Contingent

This answer should be removed. It is not relevant to the system in question... MS SQL Server. It will just lead to confusion. – Ptyalism

I got to this question thread when searching about MySQL, and therfore this answer was very useful. Thanks. And maybe update your answer to mention it's directed for MySQL – Uralic

Answer 9

Here is a script I wrote in order to automate the process. I hope it helps.

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
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')
 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
    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 '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'

Dimitri

Be careful. I would also add referential actions on the keys to your script or you will lose cascade settings. – Klara

this 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 be wrong when the referenced table cannot be truncated because it also has foreign key references. In this version only the table specified as parameter will be truncated, all referenced table should be truncated manually before calling this script I posted the updated tolution to this thread here https://mcmap.net/q/64336/-cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint – Klug

@Klara Is there any way to find those referential actions? I've been poking around the internet, and can't seem to find them. I can post it as a formal question, if you'd rather. – Cassaba

Note to future visitors: it is in the sys.foreign_keys table. (Reference) – Cassaba

@Michael: You can also use INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS (msdn.microsoft.com/en-us/library/ms179987.aspx) – Klara

Edited for my purposes to account for composite keys, multiple schemas, and cascading deletes. https://mcmap.net/q/64336/-cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint – Claraclarabella

Answer 10

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.

Duwalt

Keep in mind that even though this works nicely, the transaction log will increase by the number of records in the table vs. 'truncate table' which only put one record in the transaction log. Not a big deal for most tables but if there are millions+ rows then it could be an issue. – Foreword

Answer 11

@denver_citizen and @Peter Szanto's answers didn't quite work for me, but I modified them to account for:

1. Composite Keys
2. On Delete and On Update actions
3. Checking the index when re-adding
4. Schemas other than dbo
5. Multiple tables at once

DECLARE @Debug bit = 0;

-- List of tables to truncate
select
    SchemaName, Name
into #tables
from (values 
    ('schema', 'table')
    ,('schema2', 'table2')
) as X(SchemaName, Name)


BEGIN TRANSACTION TruncateTrans;

with foreignKeys AS (
     SELECT 
        SCHEMA_NAME(fk.schema_id) as SchemaName
        ,fk.Name as ConstraintName
        ,OBJECT_NAME(fk.parent_object_id) as TableName
        ,SCHEMA_NAME(t.SCHEMA_ID) as ReferencedSchemaName
        ,OBJECT_NAME(fk.referenced_object_id) as ReferencedTableName
        ,fc.constraint_column_id
        ,COL_NAME(fk.parent_object_id, fc.parent_column_id) AS ColumnName
        ,COL_NAME(fk.referenced_object_id, fc.referenced_column_id) as ReferencedColumnName
        ,fk.delete_referential_action_desc
        ,fk.update_referential_action_desc
    FROM sys.foreign_keys AS fk
        JOIN sys.foreign_key_columns AS fc
            ON fk.object_id = fc.constraint_object_id
        JOIN #tables tbl 
            ON OBJECT_NAME(fc.referenced_object_id) = tbl.Name
        JOIN sys.tables t on OBJECT_NAME(t.object_id) = tbl.Name 
            and SCHEMA_NAME(t.schema_id) = tbl.SchemaName
            and t.OBJECT_ID = fc.referenced_object_id
)



select
    quotename(fk.ConstraintName) AS ConstraintName
    ,quotename(fk.SchemaName) + '.' + quotename(fk.TableName) AS TableName
    ,quotename(fk.ReferencedSchemaName) + '.' + quotename(fk.ReferencedTableName) AS ReferencedTableName
    ,replace(fk.delete_referential_action_desc, '_', ' ') AS DeleteAction
    ,replace(fk.update_referential_action_desc, '_', ' ') AS UpdateAction
    ,STUFF((
        SELECT ',' + quotename(fk2.ColumnName)
        FROM foreignKeys fk2 
        WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
        ORDER BY fk2.constraint_column_id
        FOR XML PATH('')
    ),1,1,'') AS ColumnNames
    ,STUFF((
        SELECT ',' + quotename(fk2.ReferencedColumnName)
        FROM foreignKeys fk2 
        WHERE fk2.ConstraintName = fk.ConstraintName and fk2.SchemaName = fk.SchemaName
        ORDER BY fk2.constraint_column_id
        FOR XML PATH('')
    ),1,1,'') AS ReferencedColumnNames
into #FKs
from foreignKeys fk
GROUP BY fk.SchemaName, fk.ConstraintName, fk.TableName, fk.ReferencedSchemaName, fk.ReferencedTableName, fk.delete_referential_action_desc, fk.update_referential_action_desc



-- Drop FKs
select 
    identity(int,1,1) as ID,
    'ALTER TABLE ' + fk.TableName + ' DROP CONSTRAINT ' + fk.ConstraintName AS script
into #scripts
from #FKs fk

-- Truncate 
insert into #scripts
select distinct 
    'TRUNCATE TABLE ' + quotename(tbl.SchemaName) + '.' + quotename(tbl.Name) AS script
from #tables tbl

-- Recreate
insert into #scripts
select 
    'ALTER TABLE ' + fk.TableName + 
    ' WITH CHECK ADD CONSTRAINT ' + fk.ConstraintName + 
    ' FOREIGN KEY ('+ fk.ColumnNames +')' + 
    ' REFERENCES ' + fk.ReferencedTableName +' ('+ fk.ReferencedColumnNames +')' +
    ' ON DELETE ' + fk.DeleteAction COLLATE Latin1_General_CI_AS_KS_WS + ' ON UPDATE ' + fk.UpdateAction COLLATE Latin1_General_CI_AS_KS_WS AS script
from #FKs fk


DECLARE @script nvarchar(MAX);

DECLARE curScripts CURSOR FOR 
    select script
    from #scripts
    order by ID

OPEN curScripts

WHILE 1=1 BEGIN
    FETCH NEXT FROM curScripts INTO @script
    IF @@FETCH_STATUS != 0 BREAK;

    print @script;
    IF @Debug = 0
        EXEC (@script);
END
CLOSE curScripts
DEALLOCATE curScripts


drop table #scripts
drop table #FKs
drop table #tables


COMMIT TRANSACTION TruncateTrans;

Claraclarabella

Nice, careful code. I would just return the script code without an option to run it. Maybe that would dispense of the cursor. Also it should truncate parent tables automatically. – Benign

Answer 12

You cannot truncate a table if you don't drop the constraints. A disable also doesn't work. you need to Drop everything. i've made a script that drop all constrainsts and then recreate then.

Be sure to wrap it in a transaction ;)

SET NOCOUNT ON
GO

DECLARE @table TABLE(
RowId INT PRIMARY KEY IDENTITY(1, 1),
ForeignKeyConstraintName NVARCHAR(200),
ForeignKeyConstraintTableSchema NVARCHAR(200),
ForeignKeyConstraintTableName NVARCHAR(200),
ForeignKeyConstraintColumnName NVARCHAR(200),
PrimaryKeyConstraintName NVARCHAR(200),
PrimaryKeyConstraintTableSchema NVARCHAR(200),
PrimaryKeyConstraintTableName NVARCHAR(200),
PrimaryKeyConstraintColumnName NVARCHAR(200)
)

INSERT INTO @table(ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName)
SELECT
U.CONSTRAINT_NAME,
U.TABLE_SCHEMA,
U.TABLE_NAME,
U.COLUMN_NAME
FROM
INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON U.CONSTRAINT_NAME = C.CONSTRAINT_NAME
WHERE
C.CONSTRAINT_TYPE = 'FOREIGN KEY'

UPDATE @table SET
PrimaryKeyConstraintName = UNIQUE_CONSTRAINT_NAME
FROM
@table T
INNER JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
ON T.ForeignKeyConstraintName = R.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintTableSchema = TABLE_SCHEMA,
PrimaryKeyConstraintTableName = TABLE_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS C
ON T.PrimaryKeyConstraintName = C.CONSTRAINT_NAME

UPDATE @table SET
PrimaryKeyConstraintColumnName = COLUMN_NAME
FROM @table T
INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE U
ON T.PrimaryKeyConstraintName = U.CONSTRAINT_NAME

--DROP CONSTRAINT:

DECLARE @dynSQL varchar(MAX);

DECLARE cur CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
DROP CONSTRAINT ' + ForeignKeyConstraintName + '
'
FROM
@table

OPEN cur

FETCH cur into @dynSQL
WHILE @@FETCH_STATUS = 0 
BEGIN
    exec(@dynSQL)
    print @dynSQL

    FETCH cur into @dynSQL
END
CLOSE cur
DEALLOCATE cur
---------------------



   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!

    truncate table your_table

   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!
   --HERE GOES YOUR TRUNCATES!!!!!

---------------------
--ADD CONSTRAINT:

DECLARE cur2 CURSOR FOR
SELECT
'
ALTER TABLE [' + ForeignKeyConstraintTableSchema + '].[' + ForeignKeyConstraintTableName + ']
ADD CONSTRAINT ' + ForeignKeyConstraintName + ' FOREIGN KEY(' + ForeignKeyConstraintColumnName + ') REFERENCES [' + PrimaryKeyConstraintTableSchema + '].[' + PrimaryKeyConstraintTableName + '](' + PrimaryKeyConstraintColumnName + ')
'
FROM
@table

OPEN cur2

FETCH cur2 into @dynSQL
WHILE @@FETCH_STATUS = 0 
BEGIN
    exec(@dynSQL)

    print @dynSQL

    FETCH cur2 into @dynSQL
END
CLOSE cur2
DEALLOCATE cur2

Madelyn

Answer 13

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.

Priscilapriscilla

I would like to 'truncate' all the schema, not drop it. I'd like to do it in the Setup method of integration tests. Calling the DB creation script from within integration tests is ... not the first solution I'll go to. – Sannyasi

Answer 14

Found elsewhere on the web

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
-- EXEC sp_MSForEachTable 'DELETE FROM ?' -- Uncomment to execute
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
EXEC sp_MSForEachTable 'ALTER TABLE ? ENABLE TRIGGER ALL'

Bowlds

Should probably be 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'. – Alcaeus

-1: Just confirmed this doesn't work at all with the truncate command as asked by the question. See #3844306 – Galibi

Answer 15

I write the following ways and tried to parameterized them, so you can Run them in a Query document Or Make a useful SP with them easily.

A) Delete

If your table has not millions of records this works good and hasn't any Alter commands:

---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'         --< Db Name
DECLARE @Schema AS NVARCHAR(30) = 'dbo'          --< Schema
DECLARE @TableName AS NVARCHAR(30) = 'Book'      --< Table Name
------------------ /Just Fill Parameters Value ----------------

DECLARE @Query AS NVARCHAR(500) = 'Delete FROM ' + @TableName

EXECUTE sp_executesql @Query
SET @Query=@DbName+'.'+@Schema+'.'+@TableName
DBCC CHECKIDENT (@Query,RESEED, 0)

• In above answer of mine the method of resolve the mentioned problem in the question is based on @s15199d answer.

B) Truncate

If your table has millions of records or you hasn't any problem with Alter command in your codes, then use this one:

--   Book                               Student
--
--   |  BookId  | Field1 |              | StudentId |  BookId  |
--   ---------------------              ------------------------ 
--   |    1     |    A   |              |     2     |    1     |  
--   |    2     |    B   |              |     1     |    1     |
--   |    3     |    C   |              |     2     |    3     |  

---------------------------------------------------------------
------------------- Just Fill Parameters Value ----------------
---------------------------------------------------------------
DECLARE @DbName AS NVARCHAR(30) = 'MyDb'
DECLARE @Schema AS NVARCHAR(30) = 'dbo'
DECLARE @TableName_ToTruncate AS NVARCHAR(30) = 'Book'

DECLARE @TableName_OfOwnerOfConstraint AS NVARCHAR(30) = 'Student' --< Decelations About FK_Book_Constraint
DECLARE @Ref_ColumnName_In_TableName_ToTruncate AS NVARCHAR(30) = 'BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ColumnName_In_TableOfOwnerOfConstraint AS NVARCHAR(30) = 'Fk_BookId' --< Decelations About FK_Book_Constraint
DECLARE @FK_ConstraintName AS NVARCHAR(30) = 'FK_Book_Constraint'                --< Decelations About FK_Book_Constraint
------------------ /Just Fill Parameters Value ----------------

DECLARE @Query AS NVARCHAR(2000)

SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' DROP CONSTRAINT '+@FK_ConstraintName
EXECUTE sp_executesql @Query

SET @Query= 'Truncate Table '+ @TableName_ToTruncate
EXECUTE sp_executesql @Query

SET @Query= 'ALTER TABLE '+@TableName_OfOwnerOfConstraint+' ADD CONSTRAINT '+@FK_ConstraintName+' FOREIGN KEY('+@FK_ColumnName_In_TableOfOwnerOfConstraint+') REFERENCES '+@TableName_ToTruncate+'('+@Ref_ColumnName_In_TableName_ToTruncate+')'
EXECUTE sp_executesql @Query

• In above answer of mine the method of resolve the mentioned problem in the question is based on @LauroWolffValenteSobrinho answer.

• If you have more than one CONSTRAINT then you should append its codes like me to the above query

• Also you can change the above code base @SerjSagan answer to disable an enable the constraint

Sexy

Answer 16

truncate did not work for me, delete + reseed is the best way out. In case there are some of you out there who need to iterate over huge number of tables to perform delete + reseed, you might run into issues with some tables which does not have an identity column, the following code checks if identity column exist before attempting to reseed

EXEC ('DELETE FROM [schemaName].[tableName]')
IF EXISTS (Select * from sys.identity_columns where object_name(object_id) = 'tableName')
BEGIN
    EXEC ('DBCC CHECKIDENT ([schemaName.tableName], RESEED, 0)')
END

Rag

Answer 17

For MS SQL, at least the newer versions, you can just disable the constrains with code like this:

ALTER TABLE Orders
NOCHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO

TRUNCATE TABLE Customers
GO

ALTER TABLE Orders
WITH CHECK CHECK CONSTRAINT [FK_dbo.Orders_dbo.Customers_Customer_Id]
GO

Sallie

I think we established above that this doesn't work? Maybe it does for newer versions? – Harpoon

Fwiw, this is not working in the OP's version (2005), and also not working in its successor (MSSQL2008). – Plymouth

Answer 18

The only way is to drop foreign keys before doing the truncate. And after truncating the data, you must re-create the indexes.

The following script generates the required SQL for dropping all foreign key constraints.

DECLARE @drop NVARCHAR(MAX) = N'';

SELECT @drop += N'
ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
    + ' DROP CONSTRAINT ' + QUOTENAME(fk.name) + ';'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS ct
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id];

SELECT @drop

Next, the following script generates the required SQL for re-creating foreign keys.

DECLARE @create NVARCHAR(MAX) = N'';

SELECT @create += N'
ALTER TABLE ' 
   + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) 
   + ' ADD CONSTRAINT ' + QUOTENAME(fk.name) 
   + ' FOREIGN KEY (' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the columns in the constraint table
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.parent_column_id = c.column_id
    AND fkc.parent_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'')
  + ') REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
  + '(' + STUFF((SELECT ',' + QUOTENAME(c.name)
   -- get all the referenced columns
    FROM sys.columns AS c 
    INNER JOIN sys.foreign_key_columns AS fkc 
    ON fkc.referenced_column_id = c.column_id
    AND fkc.referenced_object_id = c.[object_id]
    WHERE fkc.constraint_object_id = fk.[object_id]
    ORDER BY fkc.constraint_column_id 
    FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 1, N'') + ');'
FROM sys.foreign_keys AS fk
INNER JOIN sys.tables AS rt -- referenced table
  ON fk.referenced_object_id = rt.[object_id]
INNER JOIN sys.schemas AS rs 
  ON rt.[schema_id] = rs.[schema_id]
INNER JOIN sys.tables AS ct -- constraint table
  ON fk.parent_object_id = ct.[object_id]
INNER JOIN sys.schemas AS cs 
  ON ct.[schema_id] = cs.[schema_id]
WHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;

SELECT @create

Run the generated script to drop all foreign keys, truncate tables, and then run the generated script to re-create all foreign keys.

The queries are taken from here.

Thirtieth

Answer 19

It's my solution of this issue. I used it for altering PK, but idea the same. Hope this will be useful)

PRINT 'Script starts'

DECLARE @foreign_key_name varchar(255)
DECLARE @keycnt int
DECLARE @foreign_table varchar(255)
DECLARE @foreign_column_1 varchar(255)
DECLARE @foreign_column_2 varchar(255)
DECLARE @primary_table varchar(255)
DECLARE @primary_column_1 varchar(255)
DECLARE @primary_column_2 varchar(255)
DECLARE @TablN varchar(255)

-->> Type the primary table name
SET @TablN = ''
---------------------------------------------------------------------------------------    ------------------------------
--Here will be created the temporary table with all reference FKs
---------------------------------------------------------------------------------------------------------------------
PRINT 'Creating the temporary table'
select cast(f.name  as varchar(255)) as foreign_key_name
    , r.keycnt
    , cast(c.name as  varchar(255)) as foreign_table
    , cast(fc.name as varchar(255)) as  foreign_column_1
    , cast(fc2.name as varchar(255)) as foreign_column_2
    , cast(p.name as varchar(255)) as primary_table
    , cast(rc.name as varchar(255))  as primary_column_1
    , cast(rc2.name as varchar(255)) as  primary_column_2
    into #ConTab
    from sysobjects f
    inner join sysobjects c on  f.parent_obj = c.id 
    inner join sysreferences r on f.id =  r.constid
    inner join sysobjects p on r.rkeyid = p.id
    inner  join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid
    inner  join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid
    left join  syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc.colid
    left join  syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc.colid
    where f.type =  'F' and p.name = @TablN
 ORDER BY cast(p.name as varchar(255))
---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will drop all reference FKs
---------------------------------------------------------------------------------------------------------------------
DECLARE @CURSOR CURSOR
/*Fill in cursor*/

PRINT 'Cursor 1 starting. All refernce FK will be droped'

SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table,         @foreign_column_1, @foreign_column_2, 
                        @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('ALTER TABLE ['+@foreign_table+'] DROP CONSTRAINT ['+@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 1 finished work'
---------------------------------------------------------------------------------------------------------------------
--Here you should provide the chainging script for the primary table
---------------------------------------------------------------------------------------------------------------------

PRINT 'Altering primary table begin'

TRUNCATE TABLE table_name

PRINT 'Altering finished'

---------------------------------------------------------------------------------------------------------------------
--Cursor, below, will add again all reference FKs
--------------------------------------------------------------------------------------------------------------------

PRINT 'Cursor 2 starting. All refernce FK will added'
SET @CURSOR  = CURSOR SCROLL
FOR
select foreign_key_name
    , keycnt
    , foreign_table
    , foreign_column_1
    , foreign_column_2
    , primary_table
    , primary_column_1
    , primary_column_2
    from #ConTab

OPEN @CURSOR

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2

WHILE @@FETCH_STATUS = 0
BEGIN

    EXEC ('ALTER TABLE [' +@foreign_table+ '] WITH NOCHECK ADD  CONSTRAINT [' +@foreign_key_name+ '] FOREIGN KEY(['+@foreign_column_1+'])
        REFERENCES [' +@primary_table+'] (['+@primary_column_1+'])')

    EXEC ('ALTER TABLE [' +@foreign_table+ '] CHECK CONSTRAINT [' +@foreign_key_name+']')

FETCH NEXT FROM @CURSOR INTO @foreign_key_name, @keycnt, @foreign_table, @foreign_column_1, @foreign_column_2, 
                         @primary_table, @primary_column_1, @primary_column_2
END
CLOSE @CURSOR
PRINT 'Cursor 2 finished work'
---------------------------------------------------------------------------------------------------------------------
PRINT 'Temporary table droping'
drop table #ConTab
PRINT 'Finish'

Granese

Answer 20

The following works for me even with FK constraints, and combines the following answers to only drop the specified tables:

• Transaction Automatic Rollback
• Looping through comma-separated list
• Executing Dynamic SQL (with table names from variable)
• DELETE and RESEED table (in this thread)

---

USE [YourDB];

DECLARE @TransactionName varchar(20) = 'stopdropandroll';

BEGIN TRAN @TransactionName;
set xact_abort on; /* automatic rollback https://mcmap.net/q/65716/-sql-server-transactions-roll-back-on-error */
    -- ===== DO WORK // =====

    -- dynamic sql placeholder
    DECLARE @SQL varchar(300);

    -- LOOP: https://mcmap.net/q/65717/-comma-separated-list-in-sql
    -- list of things to loop
    DECLARE @delim char = ';';
    DECLARE @foreach varchar(MAX) = 'Table;Names;Separated;By;Delimiter' + @delim + 'AnotherName' + @delim + 'Still Another';
    DECLARE @token varchar(MAX);
    WHILE len(@foreach) > 0
    BEGIN
        -- set current loop token
        SET @token = left(@foreach, charindex(@delim, @foreach+@delim)-1)
        -- ======= DO WORK // ===========

        -- dynamic sql (parentheses are required): https://mcmap.net/q/65718/-using-a-variable-for-table-name-in-39-from-39-clause-in-sql-server-2008
        SET @SQL = 'DELETE FROM [' + @token + ']; DBCC CHECKIDENT (''' + @token + ''',RESEED, 0);'; -- https://stackoverflow.com/a/11784890
        PRINT @SQL;
        EXEC (@SQL);

        -- ======= // END WORK ===========
        -- continue loop, chopping off token
        SET @foreach = stuff(@foreach, 1, charindex(@delim, @foreach+@delim), '')
    END

    -- ===== // END WORK =====
-- review and commit
SELECT @@TRANCOUNT as TransactionsPerformed, @@ROWCOUNT as LastRowsChanged;
COMMIT TRAN @TransactionName;

---

Note:

I think it still helps to declare the tables in the order you want them deleted (i.e. kill dependencies first). As seen in this answer, rather than loop specific names you could substitute all tables with

EXEC sp_MSForEachTable 'DELETE FROM ?; DBCC CHECKIDENT (''?'',RESEED, 0);';

Pillowcase

Didn't really tried to other scripts since everyone stated that they don't work when you have Foreign Keys. So i tried this one and this one did the trick for me. – Rockwell

DELETE is not the same as TRUNCATE. This will fill up your transaction logs. – Lifetime

@Dan, probably a good point; as I mentioned I just combined the other answers around here... – Pillowcase

@Pillowcase It will work fine for small/medium tables, but I've had a production SQL server go offline due to a delete command filling up the transaction log, which filled the hard disk. At the very least, make sure your transactions logs have a max size before attempting this on a large table. – Lifetime

Answer 21

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!

Trimly

any sql sample about it ? – Glissando

Answer 22

Delete then reset auto-increment:

delete from tablename;

then

ALTER TABLE tablename AUTO_INCREMENT = 1;

Syverson

why is AUTO_INCREMENT saying (Incorrect syntax near 'AUTO_INCREMENT'.? – Myrta

@Myrta AUTO_INCREMENT is MySQL, not SQL Server (not TSQL) (i.e. see tags on OP) – Substation

Answer 23

The following script truncates all foreign key constraints & recreates them:

DECLARE @DROP_CONSTRAINT_SCRIPT VARCHAR(MAX);
DECLARE @CREATE_CONSTRAINT_SCRIPT VARCHAR(MAX);
SET @DROP_CONSTRAINT_SCRIPT='';
SET @CREATE_CONSTRAINT_SCRIPT='';

SELECT @DROP_CONSTRAINT_SCRIPT=@DROP_CONSTRAINT_SCRIPT + 'ALTER TABLE '+FK_Table+'
DROP CONSTRAINT '+FK_Name+'; 
',
@CREATE_CONSTRAINT_SCRIPT = @CREATE_CONSTRAINT_SCRIPT +
'ALTER TABLE '+FK_Table+'
ADD CONSTRAINT '+FK_Name+'
FOREIGN KEY ('+FK_Column+') REFERENCES '+PK_Table+'('+PK_Column+');
'
FROM (
SELECT RC.CONSTRAINT_NAME FK_Name
, KF.TABLE_SCHEMA FK_Schema
, KF.TABLE_NAME FK_Table
, KF.COLUMN_NAME FK_Column
, RC.UNIQUE_CONSTRAINT_NAME PK_Name
, KP.TABLE_SCHEMA PK_Schema
, KP.TABLE_NAME PK_Table
, KP.COLUMN_NAME PK_Column
, RC.MATCH_OPTION MatchOption
, RC.UPDATE_RULE UpdateRule
, RC.DELETE_RULE DeleteRule
FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS RC
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KF ON RC.CONSTRAINT_NAME = KF.CONSTRAINT_NAME
JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE KP ON RC.UNIQUE_CONSTRAINT_NAME = KP.CONSTRAINT_NAME
WHERE KP.TABLE_NAME='TABLE_NAME_TRUNCATE'
) TBL

-- DROP CONSTRAINTS
EXEC( @DROP_CONSTRAINT_SCRIPT);

-------
-- TRUNCATE TABLE SCRIPT NEEDS TO BE PUT BELOW
-------
TRUNCATE TABLE TABLE_NAME_TRUNCATE

-- RECREATE CONSTRAINTS
EXEC( @CREATE_CONSTRAINT_SCRIPT);

Jewbaiting

Answer 24

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.

Glyceryl

Answer 25

If you're doing this at any sort of a frequency, heck even on a schedule, I would absolutely, unequivocally never use a DML statement. The cost of writing to the transaction log is just to high, and setting the entire database into SIMPLE recovery mode to truncate one table is ridiculous.

The best way, is unfortunately the hard or laborious way. That being:

• Drop constraints
• Truncate table
• Re-create constraints

My process for doing this involves the following steps:

1. In SSMS right-click on the table in question, and select View Dependencies
2. Take note of the tables referenced (if any)
3. Back in object explorer, expand the Keys node and take note of the foreign keys (if any)
4. Start scripting (drop / truncate / re-create)

Scripts of this nature should be done within a begin tran and commit tran block.

Immaterial

Answer 26

This is an example for someone that use Entity Framework

• Table to be reset: Foo

• Another table that depends on: Bar

• Constraint Column on table Foo : FooColumn

• Constraint Column on table Bar : BarColumn

  public override void Down()
  {
      DropForeignKey("dbo.Bar", "BarColumn", "dbo.Foo");
      Sql("TRUNCATE TABLE Foo");
      AddForeignKey("dbo.Bar", "BarColumn", "dbo.Foo", "FooColumn", cascadeDelete: true);
  }
  

Swatter

Answer 27

Seeing how well this solution works, I put together a handy stored procedure for easy use

CREATE OR ALTER PROCEDURE ResetTable
(
  @database NVARCHAR(128),
  @schema NVARCHAR(128),
  @table NVARCHAR(128)
)
AS
BEGIN
  BEGIN TRANSACTION;

  DECLARE @sql NVARCHAR(MAX);

  SET @sql = 'DELETE FROM ' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ';' +
             'DBCC CHECKIDENT (''' + QUOTENAME(@database) + '.' + QUOTENAME(@schema) + '.' + QUOTENAME(@table) + ''', RESEED, 0);';

  BEGIN TRY
    EXEC sp_executesql @sql;
    COMMIT;
  END TRY
  BEGIN CATCH
    ROLLBACK;
  END CATCH;
END;

Usage Example:

EXEC ResetTable @database='MyDatabase', @schema='dbo', @table='Users';

Sierrasiesser

Answer 28

first, you should find the foreign keys that point to your table. Right-click on your table and then click on "view dependencies". there is a place where you can find the other tables related to your table. make keys by generating a script and then deleting the keys. now you can truncate your heavy table record as fast as possible. don't forget, to run the key scripts that you deleted before. in my opinion,you can use truncate only when you have to.

Mesentery

Answer 29

You could try DELETE FROM <your table >;.

The server will show you the name of the restriction and the table, and deleting that table you can delete what you need.

Muniz

Read his second phrase on the question. He knows he can do that, but that is not what he wants – Madelyn

Answer 30

SET FOREIGN_KEY_CHECKS=0;
TRUNCATE table1;
TRUNCATE table2;
SET FOREIGN_KEY_CHECKS=1;

reference - truncate foreign key constrained table

Working for me in MYSQL

Mercorr

Other than the specified version, is there anything else wrong with with this? Would it be recommended to use it, or avoid it altogether? – Sada

@AndyIbanez MySQL is a completely different product from MSSQL, not a different version of MSSQL. – Lifetime

its correct answer I don't know why everyone gives negative – Lafond

Answer 31

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;

Admiralty

This isn't valid SQL Server syntax for ALTER TABLE. There is no { ENABLE | DISABLE } CONSTRAINT. See: msdn.microsoft.com/en-us/library/ms190273.aspx – Buff

Answer 32

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

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

Lemar

OP stated MSSQL. You gave an answer exclusive to MySQL. – Lair