Data Points
Disabling Constraints and Triggers
John Papa
Code download available at:
DataPoints2007_04.exe
(153 KB)
Browse the Code Online
Contents
Constraints are valuable tools for maintaining data integrity in databases. However, there are times when it is convenient to disable one or more of them to perform tasks such as data synchronization with an offline database. When SQL Server™ replication is used to synchronize data between databases, individual objects can be told to withhold enforcement during replication.
For example, when a foreign key constraint is defined using the NOT FOR REPLICATION statement, SQL Server will not enforce the constraint during the replication process. In fact, the NOT FOR REPLICATION statement can be used directly in T-SQL statements that define foreign key constraints, check constraints, identities, and triggers. For those who use SQL Server replication, using the NOT FOR REPLICATION statement on the appropriate objects is a good option. However, if you are instead performing a manual synchronization of your data, another option is to disable the constraints and triggers manually.
Manual synchronization is often performed in online/offline applications where there is a subset of the data to be synchronized and where greater control over how the data is synchronized is required. In this month's column, I will discuss when it might be advantageous to manually disable and enable constraints, what kinds of problems this can solve, and some tricks to help you out.
Disabling Foreign Keys
I do not advocate removing foreign key constraints from a relational database. However, there are times when you may need to temporarily relieve the referential integrity checks of one or more foreign keys, such as when you're performing a large number of inserts and updates on a series of tables and want more accurate results and better performance. Of course, you should only use this technique when it is impractical to perform massive amounts of data updates to an entire database in the proper relational order.
So when might it be a good idea to disable foreign key constraints? Imagine you have a relational database structure that has dozens of tables, all of which are related to each other in some way through foreign key constraints. The application that interacts with this database has an offline/mobile version that communicates with a second instance of the database that likely resides on a laptop. Data changes that are made to the main database might need to be synchronized with the offline/mobile database, and there are several ways to handle this.
One way to synchronize the data is to apply the inserts, updates, and deletes to the offline/mobile database in the order that adheres to the relational structure. For example, insert customers before their respective orders, and orders before their order details. Record deletion would be performed in the opposite direction (from child to parent). This technique, however, can become too complex to implement and maintain on large database structures.
Another technique is to drop the foreign key constraints, synchronize the data, and then recreate the foreign key constraints. While this approach can work, a less dramatic approach is to merely disable the foreign key constraints. Once that's done, the data can be synchronized and then the foreign keys can be enabled once again. The syntax to disable a foreign key is shown here:
-- Disable foreign key constraint
ALTER TABLE Orders
NOCHECK CONSTRAINT
FK_Orders_Customers
-- Add a new Order record for a non-existent customer
INSERT INTO Orders (CustomerID) VALUES ('BLAH')
-- Select all orders for the non-existent customer
SELECT * FROM Orders WHERE CustomerID = 'BLAH'
This foreign key enforces that the CustomerID in the orders table be a valid CustomerID from the Customers table. The code disables the foreign key and then inserts an order into the Orders table. The order record that is inserted has a CustomerID that does not exist in the parent Customers table. Because the foreign key is disabled, the integrity check is bypassed and the order record is inserted successfully.
The following code demonstrates how to re-enable the foreign key constraint and then test that it is working. When this code is executed, the order is not inserted because the constraint is enforced. An error message will be returned stating that the insert statement conflicts with the foreign key constraint.
-- Enable foreign key constraint
ALTER TABLE Orders
CHECK CONSTRAINT
FK_Orders_Customers
-- Add a new Order record for a non-existent customer
INSERT INTO Orders (CustomerID) VALUES ('BLEH')
When disabling foreign keys, triggers, and other constraints, it is important to make sure that no Data Manipulation Language (DML) operations are being performed on the database during that time period. This must be handled when performing the disabling options manually. If you are instead synchronizing data with SQL Server replication and the NOT FOR REPLICATION statement, this condition is handled automatically.
Disabling Triggers
It's also possible to disable a trigger when necessary. Sometimes when synchronizing data you do not want a trigger to fire. For instance, if you are updating an offline/mobile database (as in the previous example), there may be triggers on several tables that you may not be aware of. These triggers may fire when a row is inserted into their table causing the row to be reinserted into another table. But during a large data synchronization, you might not want those types of inserts to be reinserted. To avoid that eventuality, you can temporarily disable the trigger, like so:
DISABLE TRIGGER MyTriggerName ON MyTableName
Conversely, to enable it, do this:
ENABLE TRIGGER MyTriggerName ON MyTableName
The following code shows a trigger that will fire (and display a message) when one or more rows are inserted or updated in the Region table.
CREATE TRIGGER trRegion_InsertUpdate ON Region
FOR INSERT, UPDATE
AS
PRINT ' Trigger is running. '
+ CAST(@@ROWCOUNT AS VARCHAR(10))
+ ' row(s) affected.'
GO
You can test this trigger by inserting a new region into the Region table, like so:
INSERT INTO Region (RegionID, RegionDescription)
VALUES (5, 'Some New Region')
When this code is executed in a query window, the new region is inserted into the Region table, the trigger fires, and a message is displayed in the messages pane. To disable the trigger, you can execute the following code:
DISABLE TRIGGER trRegion_InsertUpdate ON Region
If you execute this code to update the new region, the data is updated but the trigger will not fire:
UPDATE Region SET RegionDescription = 'A Great Region'
WHERE RegionID = 5
To re-enable the trigger, simply execute the following query:
ENABLE TRIGGER trRegion_InsertUpdate ON Region
Synchronizing an Offline/Mobile Database
Disabling a single trigger or foreign key has its uses, but disabling all of them in one fell swoop can be extremely useful in the offline/mobile database synchronization scenario I described. To do so, you would take the following steps: disable all foreign key constraints, disable all triggers, perform the insert, update and delete operations, enable all foreign key constraints, and finally re-enable all triggers.
By disabling the triggers and foreign key constraints, the order in which the data is modified becomes inconsequential. In a small database like Northwind, this is a minor savings; in a large database with dozens or hundreds of tables and relationships, however, this can be a huge time saver. In addition, without this technique you would have to modify the sequence of the tables in your script to insert/update/delete the data every time the database schema adds new tables and relationships. With the foreign key constraints and triggers disabled, you can just add the tables to the end of your script since order is not important.
It is critical to remember to enable the triggers and foreign keys at the end, regardless of whether the script was successful or not. For example, if your script disables the constraints and triggers and attempts to modify the data, and the attempt fails, you would still want the constraints and triggers to be enabled at the end.
Cursors and Information Schema Views
To disable all foreign keys, you must first gather a list of them and the tables to which each is applied. Here, Cursors and INFORMATION_SCHEMA views can assist you. The view named INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS will return a list of the foreign keys in the current database. To get the name of the table that the foreign key is applied to, use the view called INFORMATION_SCHEMA.TABLE_CONSTRAINTS. The following code joins these two views to return a list of all of the foreign keys and their respective tables.
SELECT
ref.constraint_name AS FK_Name,
fk.table_name AS FK_Table
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
ON ref.constraint_name = fk.constraint_name
ORDER BY
fk.table_name,
ref.constraint_name
Disabling All Foreign Keys
Now that you have your list, you can write a script to disable the foreign keys. You can declare and open a cursor, iterate through the list, create the T-SQL command that will disable a foreign key, and execute it for each foreign key (see Figure 1).
Figure 1 Disabling and Enabling All Foreign Keys
CREATE PROCEDURE pr_Disable_Triggers_v2
@disable BIT = 1
AS
DECLARE
@sql VARCHAR(500),
@tableName VARCHAR(128),
@tableSchema VARCHAR(128)
-- List of all tables
DECLARE triggerCursor CURSOR
FOR
SELECT
t.TABLE_NAME AS TableName,
t.TABLE_SCHEMA AS TableSchema
FROM
INFORMATION_SCHEMA.TABLES t
ORDER BY
t.TABLE_NAME,
t.TABLE_SCHEMA
OPEN triggerCursor
FETCH NEXT FROM triggerCursor
INTO @tableName, @tableSchema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @disable = 1
SET @sql = ‘ALTER TABLE ‘ + @tableSchema
+ ‘.[‘ + @tableName + ‘] DISABLE TRIGGER ALL’
ELSE
SET @sql = ‘ALTER TABLE ‘ + @tableSchema
+ ‘.[‘ + @tableName + ‘] ENABLE TRIGGER ALL’
PRINT ‘Executing Statement - ‘ + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor
INTO @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
First, the foreignKeyCursor cursor is declared as the SELECT statement that gathers the list of foreign keys and their table names. Next, the cursor is opened and the initial FETCH statement is executed. This FETCH statement will read the first row's data into the local variables @foreignKeyName and @tableName.
When looping through a cursor, you can check the @@FETCH_STATUS for a value of 0, which indicates that the fetch was successful. This means the loop will continue to move forward so it can get each successive foreign key from the rowset.
@@FETCH_STATUS is available to all cursors on the connection. So if you are looping through multiple cursors, it is important to check the value of @@FETCH_STATUS in the statement immediately following the FETCH statement. @@FETCH_STATUS will reflect the status for the most recent FETCH operation on the connection. Valid values for @@FETCH_STATUS are:
- 0 = FETCH was successful
- -1 = FETCH was unsuccessful
- -2 = the row that was fetched is missing
Inside the loop, the code builds the ALTER TABLE command differently depending on whether the intention is to disable or enable the foreign key constraint (using the CHECK or NOCHECK keyword). The statement is then printed as a message so its progress can be observed and then the statement is executed. Finally, when all rows have been iterated through, the stored procedure closes and deallocates the cursor.
Disabling All Triggers
The stored procedure in Figure 1 will disable or enable all foreign keys in a database. In some cases, you will want to disable all triggers during data synchronization, as well. The pr_Disable_Triggers stored procedure in Figure 2 does just that.
Figure 2 Disabling and Enabling All Triggers
CREATE PROCEDURE pr_Disable_Triggers
@disable BIT = 1
AS
DECLARE
@sql VARCHAR(500),
@tableName VARCHAR(128),
@triggerName VARCHAR(128),
@tableSchema VARCHAR(128)
-- List of all triggers and tables that exist on them
DECLARE triggerCursor CURSOR
FOR
SELECT
so_tr.name AS TriggerName,
so_tbl.name AS TableName,
t.TABLE_SCHEMA AS TableSchema
FROM
sysobjects so_tr
INNER JOIN sysobjects so_tbl ON so_tr.parent_obj = so_tbl.id
INNER JOIN INFORMATION_SCHEMA.TABLES t
ON
t.TABLE_NAME = so_tbl.name
WHERE
so_tr.type = ‘TR’
ORDER BY
so_tbl.name ASC,
so_tr.name ASC
OPEN triggerCursor
FETCH NEXT FROM triggerCursor
INTO @triggerName, @tableName, @tableSchema
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @disable = 1
SET @sql = ‘DISABLE TRIGGER [‘
+ @triggerName + ‘] ON ‘
+ @tableSchema + ‘.[‘ + @tableName + ‘]’
ELSE
SET @sql = ‘ENABLE TRIGGER [‘
+ @triggerName + ‘] ON ‘
+ @tableSchema + ‘.[‘ + @tableName + ‘]’
PRINT ‘Executing Statement - ‘ + @sql
EXECUTE ( @sql )
FETCH NEXT FROM triggerCursor
INTO @triggerName, @tableName, @tableSchema
END
CLOSE triggerCursor
DEALLOCATE triggerCursor
The pr_Disable_Triggers stored procedure gets a rowset containing all of triggers in the current database as well as the names of their respective tables (and their schema). Since there is no INFORMATION_SCHEMA.TRIGGERS view, I gather the list of triggers and the related information from a combination of the SQL Server 2005 system tables and INFORMATION_SCHEMA views, where possible.
The sysobjects system table exists in all databases and can be queried to return a list of all triggers or tables. This system table can be joined against itself to get a list of the triggers and the tables that they operate on, as shown in the declaration of the cursor in Figure 2.
If you use this stored procedure on a database such as AdventureWorks where the tables are under specific schemas, you must prefix the name of the table with the schema name. The pr_Disable_Triggers stored procedure handles this situation by also joining to the INFORMATION_SCHEMA.TABLES view, which returns the SCHEMA_NAME for the table.
Once you have written routines that will disable and re-enable the triggers and foreign keys, you can use them in a script that will modify an offline/mobile database to keep it synchronized with a main database. For example, the following script could be used, replacing the comment in the middle with the data manipulation operations.
pr_Disable_Foreign_Keys 1
pr_Disable_Triggers 1
-- Perform data operations
pr_Disable_Foreign_Keys 0
pr_Disable_Triggers 0
Disabling All Triggers Quickly
The pr_Disable_Triggers stored procedure shown in
Figure 2 could be modified to execute a slightly different T-SQL command that will disable or enable all of the triggers. There is a T-SQL statement using a flavor of the ALTER TABLE command that will disable or enable all triggers on a table. Using this technique, the pr_Disable_Triggers stored procedure can be modified to execute the following statement for each table to disable all of the triggers on that table:
ALTER TABLE MySchemaName.MyTableName DISABLE TRIGGER ALL
By using this syntax, the cursor's query would gather all of the table names but not need to get the names of the triggers themselves. Thus, the stored procedure could be modified to use this technique to loop through the list of tables and enable or disable all triggers on each table. The modified stored procedure is shown in Figure 3.
Figure 3 Disabling All Triggers with the ALTER TABLE Command
CREATE PROCEDURE pr_Disable_Foreign_Keys
@disable BIT = 1
AS
DECLARE
@sql VARCHAR(500),
@tableName VARCHAR(128),
@foreignKeyName VARCHAR(128)
-- A list of all foreign keys and table names
DECLARE foreignKeyCursor CURSOR
FOR SELECT
ref.constraint_name AS FK_Name,
fk.table_name AS FK_Table
FROM
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS ref
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS fk
ON ref.constraint_name = fk.constraint_name
ORDER BY
fk.table_name,
ref.constraint_name
OPEN foreignKeyCursor
FETCH NEXT FROM foreignKeyCursor
INTO @foreignKeyName, @tableName
WHILE ( @@FETCH_STATUS = 0 )
BEGIN
IF @disable = 1
SET @sql = ‘ALTER TABLE [‘
+ @tableName + ‘] NOCHECK CONSTRAINT [‘
+ @foreignKeyName + ‘]’
ELSE
SET @sql = ‘ALTER TABLE [‘
+ @tableName + ‘] CHECK CONSTRAINT [‘
+ @foreignKeyName + ‘]’
PRINT ‘Executing Statement - ‘ + @sql
EXECUTE(@sql)
FETCH NEXT FROM foreignKeyCursor
INTO @foreignKeyName, @tableName
END
CLOSE foreignKeyCursor
DEALLOCATE foreignKeyCursor
Wrapping Up
It is more efficient to perform multiple database operations such as those described in this article from a T-SQL script than it is to execute them all individually from a .NET application. For example, you could gather a list of foreign keys from a .NET application using ADO.NET and then execute the commands to disable each of the foreign keys. This could be extended to get the list of triggers and then disable them. All of these actions would require going back and forth between the .NET code and the database, consuming more resources than if the code were executed entirely on the database server in T-SQL.
Send your questions and comments for John to
mmdata@microsoft.com.
John Papa is a senior .NET Consultant with ASPSOFT (
aspsoft.com) and a baseball fanatic who spends most of his summer nights rooting for the Yankees with his family and his faithful dog, Kadi. John, a C# MVP, has authored several books on ADO, XML, and SQL Server, and can often be found speaking at industry conferences such as VSLive or blogging at
codebetter.com/blogs/john.papa.