How to delete or truncate table in SQL Server?

Question

I need to clear many tables (preferably truncate table). But tables have many FK constraints. I tried something like this, but failed:-

ALTER TABLE Table1 NOCHECK CONSTRAINT ALL 
TRUNCATE TABLE Table1
ALTER TABLE Table1 WITH CHECK CHECK CONSTRAINT ALL

This is the error i am getting:-

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

Please suggest me how to delete or truncate table by dropping constraints temporarily.

Answer 1

just delete them in the proper FK order:

DELETE GreatGrandChild
DELETE Child
DELETE Parent

and don't worry about dropping constraints.

sample code:

create table ParentTable (ParentID int primary key not null, RowValue varchar(10))
INSERT INTO ParentTable VALUES (1,'AAA')
INSERT INTO ParentTable VALUES (2,'BBB')

create table ChildTable (ChildID int primary key not null, ParentID int, RowValue varchar(10))

ALTER TABLE ChildTable ADD CONSTRAINT FK_ChildTable_ParentTable FOREIGN KEY
 (ParentID) REFERENCES dbo.ParentTable (ParentID) ON UPDATE  NO ACTION  ON DELETE  NO ACTION 

INSERT INTO ChildTable VALUES (10,1,'a')
INSERT INTO ChildTable VALUES (11,1,'aa')
INSERT INTO ChildTable VALUES (12,2,'b')
INSERT INTO ChildTable VALUES (13,1,'aaa')

DELETE ChildTable
DELETE ParentTable

to find the tables that depend on your table run this query:

select 
    object_name(parent_object_id) AS ReferencesYourTable
        ,object_name(referenced_object_id) AS YourTable
        ,* 
    from sys.foreign_keys 
    WHERE object_name(referenced_object_id)='YourTable'

for the above query, delete all the rows in each table listed prior to deleting YourTable.

Answer 2

Contrary to what others have posted, you can never truncate a table referenced by a foreign key. It's documented in Books Online under TRUNCATE TABLE, but trying it out yourself is a lot faster:

create table Parent (col1 int primary key)

create table Child (
    col1 int primary key, 
    col2 int, 
    constraint fk foreign key (col2) references Parent (col1)
)


-- works
truncate table Child
-- doesn't work
truncate table Parent

alter table child nocheck constraint all

-- still doesn't work, even though the FK is disabled
truncate table Parent

drop table Child
drop table Parent

The (conceptual) reason it doesn't work is that TRUNCATE is a physical operation, not a logical one. So it is not 'foreign key aware' and if you let it ignore foreign keys it would kill referential integrity.

The usual solutions (as mentioned by others) are:

Solution 1

1. Drop foreign keys
2. Truncate tables
3. Re-create foreign keys

Solution 2

1. Drop tables
2. Re-create tables

Either solution works, it's really a deployment issue as to which is easier and suits your situation better. I know you said it's a one-time task, but I would still script it, even if only as a learning experience. Solution 1 is easy in pure TSQL; solution 2 is easier using an external language.