SQL Tips by Namwar Rizvi

August 13, 2007

Avoid accidental Truncate command on table

Filed under: tips,TSQL — namwar @ 9:00 PM

Sometimes, it is very critical to make sure that no body truncate the table accidentally. Since Truncate table command is not logged therefore, it is difficult to recover the data without using specialized tools.
Here is a quick tip to avoid accidental Truncate command. Just create a dummy foreign key which refers to your critical table and that is it. Truncate table command will not be executed if there is any foreign key refers to the primary table even if there is no data in secondary table. Following is a sample code to do this:

Use tempdb
Go

—Create Primary table
Create table test1 (UserId int not null, UserName varchar(50))

–Add Primary key
ALTER TABLE dbo.test1 ADD CONSTRAINT
PK_test1 PRIMARY KEY CLUSTERED
(
UserId
)

–Create Secondary table
Create table test2 (id int)

–Add Foreign key
ALTER TABLE test2 ADD CONSTRAINT
FK_dummy FOREIGN KEY
(
Id
) REFERENCES test1
(
UserId
)

–Now try to truncate the primary table
truncate table test1

SQL server will display the following the error:

Msg 4712, Level 16, State 1, Line 3
Cannot truncate table ‘test1’ because it is being referenced by a FOREIGN KEY constraint.

Advertisements

2 Comments »

  1. TRUNCATE TABLE can be ROLLBACKed.
    Truncate Table only deallocate data pages occupied by table so therefore having small footprint on transaction log but if rollback happen it will rollback the deallocation of data pages which will bring table back to live.
    see following link.
    http://wiki.answers.com/Q/How_do_you_differentiate_between_truncate_and_delete

    Comment by Furrukh Baig — August 15, 2007 @ 5:50 PM | Reply

  2. how to aviod truncate of table

    Comment by dinesh V — February 27, 2012 @ 5:57 AM | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

Create a free website or blog at WordPress.com.

%d bloggers like this: