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'
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
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)
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)
)
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
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.