Wikis - Page Details

First published by Kapil_KK
When: 14 Apr 2013 11:47 PM
Last revision by Fernando Lugão Veltem (8MVP, Microsoft Partne)
When: 21 Apr 2013 10:43 AM
Revisions: 2
Comments: 7

Options

Can You Improve This Article?

Positively! Click Sign In to add the tip, solution, correction or comment that will help other users.

Report inappropriate content using these instructions.

Truncate a table

When we try to TRUNCATE a table whose columns are referenced by a FOREIGN KEY constraint it will always gives us an error:
Cannot truncate 'table_name' because it is being referenced by a FOREIGN KEY constraint.
But what if a column is referencing its own table itself.
Let see the below example:

CREATE TABLE dbo.Test
(
     Col_1 INT PRIMARY KEY
    ,Col_2 INT REFERENCES Test(Col_1)
    ,Col_3 INT IDENTITY
)
;
INSERT INTO dbo.Test VALUES (1,1);
INSERT INTO dbo.Test VALUES (2,2);   
INSERT INTO dbo.Test VALUES (3,3);   
GO

As we have created a table and has inserted some dummy data in table 'Test'.
Now, lets run TRUNCATE statement -

TRUNCATE TABLE Test;
GO

It will run successfully and gives message that 'Command executed successfully'
Most the people thinks that if a table contains FOREIGN key then we can't truncate that table.

en-US, SQL Server, T-SQL [Edit tags]

Products

Resources

Solutions

Updates

Trials

Related Sites

Training

Certifications

Other resources

Support options

More support

Not an IT pro?

Truncate a table

Truncate a table