Truncate a table

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.


Leave a Comment
  • Please add 8 and 8 and type the answer here:
  • Post
Wiki - Revision Comment List(Revision Comment)
Wikis - Comment List
Sort by: Published Date | Most Recent | Most Useful
Posting comments is temporarily disabled until 10:00am PST on Saturday, December 14th. Thank you for your patience.
Comments
  • please give me feedback on my first article

  • thanks for the information..:)

  • Thanks for the information :)

  • Thanks for the information :)

  • Nice little information ..thanks :)

  • Nice little information ..thanks :)

  • oops...i got error message when i posted the comment...i don't find delete option ,could someone delete my multiple comments....

Page 1 of 1 (7 items)