SQL Server administration and T-SQL development, Web Programming with ASP.NET, HTML5 and Javascript, Windows Phone 8 app development, SAP Smartforms and ABAP Programming, Windows 7, Visual Studio and MS Office software
Development resources, articles, tutorials, code samples, tools and downloads for ASP.Net, SQL Server, Reporting Services, T-SQL, Windows, AWS, SAP HANA and ABAP


Prevent Truncate Table using Foreign Key Constraint on SQL Server

To prevent Truncate Table command to delete all data in a database table SQL Server database administrator and SQL developer can create Foreign Key Constraint referencing master table from a dummy table.

Let's use following SQL Create Table script to create a sample database table to use in our SQL tutorial . We will execute "Truncate Table" command for this sample SQL database table.

create table mySQLTable (
 id int identity(1,1),
 code nvarchar(5),
 value varchar(100)
)
insert into mySQLTable select 'SQL','www.kodyaz.com'
Code

To prevent a database user to delete records all records stored in a database table using the SQL Truncate Table command, we have to develop a strategy. Otherwise, following SQL command will delete all data in the database table.

truncate table mySQLTable
Code

To prevent a SQL database table to lose its all data by an accidentally or on purpose execution of Truncate table command, database professionals can simply create a Foreign Key constraint on an other dummy database table event without any data but only referencing to main table.

Here is how we will implement this solution in our tutorial against an accidental Truncate Table command execution to avoid data loss.

In order to prevent Truncate table execution successfully on our sample database table, database administrator or SQL developer should create a new dummy table with no data in it. But a foreign key constraint should be defined on the second dummy table referencing the look up or master table primary key field.

As you can understand the reason, there must be a primary key defined on the original table which we want to keep it from Truncate table command. Otherwise during foreign key creation to this table, SQL Engine will raise an error. Let's define the primary key on our sample mySQLTable SQL database table

alter table mySQLTable add primary key (id)
Code

Now we are ready to create our helper table in SQL Server database which will be referencing the master table with a foreign key definition.

create table truncate_protection_table (
 id int,
 constraint FK_mySQLTable_Id foreign key(id) references mySQLTable(id)
)
Code

Foreign Key Constraint between database tables in SQL Server

Now we are ready to test our strategy to prevent data loss that can occur after Truncate Table command on a specific table

truncate table mySQLTable
Code

The result of the above SQL command after the protection table is created with foreign key referencing master data table is as follows:

Msg 4712, Level 16, State 1, Line 18
Cannot truncate table 'mySQLTable' because it is being referenced by a FOREIGN KEY constraint.

This error protects the referenced database table to be truncated accidentally and help database administrators and developers avoid from data loss.

An other data protection is against SQL DELETE command using Instead of Delete Trigger. Database professionals can apply referred method using Instead of database trigger especially for lookup tables where no data has to be deleted normally by users of the database application.



SQL Server

SQL Server 2019 Installation
download SQL Server 2019
download SQL Server 2017
download SQL Server 2016
download SQL Server 2014
download SQL Server 2012
MacOS ve SQL Server 2019


Copyright © 2004 - 2021 Eralper YILMAZ. All rights reserved.