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

User · Answer

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;

User · Answer

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    You probably already have a  create schema  script  Re-using it for test isolation is easy  Creating a schema is pretty quick  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

User · Answer

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   -- gt  gt  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

User · Answer

Well  since I did not find examples of the very simple solution I used  which is    Drop foreign key  Truncate table 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

User · Answer

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

User · Answer

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

User · Answer

Correct  you cannot truncate a table which has an FK constraint on it   Typically my process for this is    Drop the constraints Trunc the table 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

User · Answer

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

User · Answer

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    In SSMS right-click on the table in question  and select View Dependencies Take note of the tables referenced  if any  Back in object explorer  expand the Keys node and take note of the foreign keys  if any  Start scripting  drop   truncate   re-create    Scripts of this nature should be done within a begin tran and commit tran block

User · Answer

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

User · Answer

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          -- lt  Db Name DECLARE  Schema AS NVARCHAR 30     dbo           -- lt  Schema DECLARE  TableName AS NVARCHAR 30     Book       -- lt  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  -- lt  Decelations About FK Book Constraint DECLARE  Ref ColumnName In TableName ToTruncate AS NVARCHAR 30     BookId  -- lt  Decelations About FK Book Constraint DECLARE  FK ColumnName In TableOfOwnerOfConstraint AS NVARCHAR 30     Fk BookId  -- lt  Decelations About FK Book Constraint DECLARE  FK ConstraintName AS NVARCHAR 30     FK Book Constraint                 -- lt  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

User · Answer

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

User · Answer

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    lt tablename gt    WITH NOCHECK ADD  CONSTRAINT   lt constraintname gt   FOREIGN KEY   lt column gt    REFERENCES  dbo    lt reftable gt      lt refcolumn gt         SET  DropStatement    ALTER TABLE  dbo    lt tablename gt   DROP CONSTRAINT   lt constraintname gt        SET  TruncateStatement    TRUNCATE TABLE   lt tablename gt     -- 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  lt    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   lt tablename gt    TableName    lt constraintname gt    ConstraintName      SET  CreateStatementTemp   REPLACE REPLACE REPLACE REPLACE REPLACE  CreateStatement   lt tablename gt    TableName    lt column gt    ColumnName    lt constraintname gt    ConstraintName    lt reftable gt    ReferencedTableName    lt refcolumn gt    ReferencedColumnName      SET  TruncateStatementTemp   REPLACE  TruncateStatement   lt tablename gt    TableName        INSERT INTO  Internal FK Definition Storage                          SELECT  ConstraintName   CreateStatementTemp   DropStatementTemp   TruncateStatementTemp      SET  i    i   1      IF  Verbose   1        PRINT     gt  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  lt    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     gt  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  lt    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     gt       Statement           END                    IF  Verbose   1        PRINT     gt  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  lt    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     gt  Re-creating       ConstraintName                  END      IF  Verbose   1        PRINT  6  Process Completed    END

User · Answer

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    lt tablename gt    WITH NOCHECK ADD  CONSTRAINT   lt constraintname gt   FOREIGN KEY   lt column gt    REFERENCES  dbo    lt reftable gt      lt refcolumn gt         SET  DropStatement    ALTER TABLE  dbo    lt tablename gt   DROP CONSTRAINT   lt constraintname gt        SET  TruncateStatement    TRUNCATE TABLE   lt tablename gt     -- 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  lt    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   lt tablename gt    TableName    lt constraintname gt    ConstraintName      SET  CreateStatementTemp   REPLACE REPLACE REPLACE REPLACE REPLACE  CreateStatement   lt tablename gt    TableName    lt column gt    ColumnName    lt constraintname gt    ConstraintName    lt reftable gt    ReferencedTableName    lt refcolumn gt    ReferencedColumnName      SET  TruncateStatementTemp   REPLACE  TruncateStatement   lt tablename gt    TableName        INSERT INTO  Internal FK Definition Storage                          SELECT  ConstraintName   CreateStatementTemp   DropStatementTemp   TruncateStatementTemp      SET  i    i   1      IF  Verbose   1        PRINT     gt  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  lt    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     gt  Dropping       ConstraintName                    END           IF  Verbose   1        PRINT  4  Truncating Tables          -- TRUNCATE TABLES     SET  i   1     WHILE   i  lt    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     gt       Statement           END      IF  Verbose   1        PRINT  5  Re-creating Foreign Keys          -- CREATE FOREING KEYS     SET  i   1     WHILE   i  lt    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     gt  Re-creating       ConstraintName                 END      IF  Verbose   1        PRINT  6  Process Completed

User · Answer

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

User · Answer

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

User · Answer

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;

User · Answer

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    lt tablename gt    WITH NOCHECK ADD  CONSTRAINT   lt constraintname gt   FOREIGN KEY   lt column gt    REFERENCES  dbo    lt reftable gt      lt refcolumn gt         SET  DropStatement    ALTER TABLE  dbo    lt tablename gt   DROP CONSTRAINT   lt constraintname gt        SET  TruncateStatement    TRUNCATE TABLE   lt tablename gt     -- 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  lt    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   lt tablename gt    TableName    lt constraintname gt    ConstraintName      SET  CreateStatementTemp   REPLACE REPLACE REPLACE REPLACE REPLACE  CreateStatement   lt tablename gt    TableName    lt column gt    ColumnName    lt constraintname gt    ConstraintName    lt reftable gt    ReferencedTableName    lt refcolumn gt    ReferencedColumnName      SET  TruncateStatementTemp   REPLACE  TruncateStatement   lt tablename gt    TableName        INSERT INTO  Internal FK Definition Storage                          SELECT  ConstraintName   CreateStatementTemp   DropStatementTemp   TruncateStatementTemp      SET  i    i   1      IF  Verbose   1        PRINT     gt  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  lt    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     gt  Dropping       ConstraintName                    END           IF  Verbose   1        PRINT  4  Truncating Tables          -- TRUNCATE TABLES     SET  i   1     WHILE   i  lt    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     gt       Statement           END      IF  Verbose   1        PRINT  5  Re-creating Foreign Keys          -- CREATE FOREING KEYS     SET  i   1     WHILE   i  lt    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     gt  Re-creating       ConstraintName                 END      IF  Verbose   1        PRINT  6  Process Completed

User · Answer

denver citizen and  Peter Szanto s answers didn t quite work for me  but I modified them to account for    Composite Keys On Delete and On Update actions Checking the index when re-adding  Schemas other than dbo 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

User · Answer

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

User · Answer

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

User · Answer

SET FOREIGN KEY CHECKS   0    truncate table  yourTableName    SET FOREIGN KEY CHECKS   1

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

Correct  you cannot truncate a table which has an FK constraint on it   Typically my process for this is    Drop the constraints Trunc the table 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

User · Answer

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

User · Answer

If none of these answers worked like in my case do this    Drop constraints Set all values to allow nulls Truncate table Add constraints that were dropped    Good luck

User · Answer

SET FOREIGN KEY CHECKS   0    truncate table  yourTableName    SET FOREIGN KEY CHECKS   1

User · Answer

Correct  you cannot truncate a table which has an FK constraint on it   Typically my process for this is    Drop the constraints Trunc the table 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

User · Answer

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

User · Answer

Delete then reset auto-increment   delete from tablename    then  ALTER TABLE tablename AUTO INCREMENT   1

User · Answer

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          -- lt  Db Name DECLARE  Schema AS NVARCHAR 30     dbo           -- lt  Schema DECLARE  TableName AS NVARCHAR 30     Book       -- lt  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  -- lt  Decelations About FK Book Constraint DECLARE  Ref ColumnName In TableName ToTruncate AS NVARCHAR 30     BookId  -- lt  Decelations About FK Book Constraint DECLARE  FK ColumnName In TableOfOwnerOfConstraint AS NVARCHAR 30     Fk BookId  -- lt  Decelations About FK Book Constraint DECLARE  FK ConstraintName AS NVARCHAR 30     FK Book Constraint                 -- lt  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

User · Answer

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

User · Answer

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    lt tablename gt    WITH NOCHECK ADD  CONSTRAINT   lt constraintname gt   FOREIGN KEY   lt column gt    REFERENCES  dbo    lt reftable gt      lt refcolumn gt         SET  DropStatement    ALTER TABLE  dbo    lt tablename gt   DROP CONSTRAINT   lt constraintname gt        SET  TruncateStatement    TRUNCATE TABLE   lt tablename gt     -- 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  lt    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   lt tablename gt    TableName    lt constraintname gt    ConstraintName      SET  CreateStatementTemp   REPLACE REPLACE REPLACE REPLACE REPLACE  CreateStatement   lt tablename gt    TableName    lt column gt    ColumnName    lt constraintname gt    ConstraintName    lt reftable gt    ReferencedTableName    lt refcolumn gt    ReferencedColumnName      SET  TruncateStatementTemp   REPLACE  TruncateStatement   lt tablename gt    TableName        INSERT INTO  Internal FK Definition Storage                          SELECT  ConstraintName   CreateStatementTemp   DropStatementTemp   TruncateStatementTemp      SET  i    i   1      IF  Verbose   1        PRINT     gt  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  lt    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     gt  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  lt    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     gt       Statement           END                    IF  Verbose   1        PRINT     gt  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  lt    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     gt  Re-creating       ConstraintName                  END      IF  Verbose   1        PRINT  6  Process Completed    END

User · Answer

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

User · Answer

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   stackoverflow com a 1749788 1037948        --       DO WORK               -- dynamic sql placeholder     DECLARE  SQL varchar 300        -- LOOP  https   stackoverflow com a 10031803 1037948     -- 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   gt  0     BEGIN         -- set current loop token         SET  token   left  foreach  charindex  delim   foreach  delim -1          --         DO WORK                         -- dynamic sql  parentheses are required   https   stackoverflow com a 989111 1037948         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

User · Answer

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   -- gt  gt  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

User · Answer

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

User · Answer

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    You probably already have a  create schema  script  Re-using it for test isolation is easy  Creating a schema is pretty quick  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

User · Answer

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

User · Answer

Well  since I did not find examples of the very simple solution I used  which is    Drop foreign key  Truncate table 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

User · Answer

You could try DELETE FROM  lt your table  gt     The server will show you the name of the restriction and the table  and deleting that table you can delete what you need

User · Answer

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

User · Answer

You could try DELETE FROM  lt your table  gt     The server will show you the name of the restriction and the table  and deleting that table you can delete what you need

User · Answer

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

User · Answer

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

User · Answer

Delete then reset auto-increment   delete from tablename    then  ALTER TABLE tablename AUTO INCREMENT   1

User · Answer

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

User · Answer

Correct  you cannot truncate a table which has an FK constraint on it   Typically my process for this is    Drop the constraints Trunc the table 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

User · Answer

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

User · Answer

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

User · Answer

denver citizen and  Peter Szanto s answers didn t quite work for me  but I modified them to account for    Composite Keys On Delete and On Update actions Checking the index when re-adding  Schemas other than dbo 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

User · Answer

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

User · Answer

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

User · Answer

If none of these answers worked like in my case do this    Drop constraints Set all values to allow nulls Truncate table Add constraints that were dropped    Good luck

User · Answer

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   stackoverflow com a 1749788 1037948        --       DO WORK               -- dynamic sql placeholder     DECLARE  SQL varchar 300        -- LOOP  https   stackoverflow com a 10031803 1037948     -- 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   gt  0     BEGIN         -- set current loop token         SET  token   left  foreach  charindex  delim   foreach  delim -1          --         DO WORK                         -- dynamic sql  parentheses are required   https   stackoverflow com a 989111 1037948         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

User · Answer

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

User · Answer

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

User · Answer

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    In SSMS right-click on the table in question  and select View Dependencies Take note of the tables referenced  if any  Back in object explorer  expand the Keys node and take note of the foreign keys  if any  Start scripting  drop   truncate   re-create    Scripts of this nature should be done within a begin tran and commit tran block

[sql-server] Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

Examples related to sql-server

Examples related to sql-server-2005

Examples related to tsql

Examples related to truncate

Examples related to foreign-keys