THE SQL Server Blog Spot on the Web

Welcome to SQLblog.com - The SQL Server blog spot on the web Sign in | |
in Search

Louis Davidson

Utility to temporarily drop FOREIGN KEY constraints on a set of tables

I was working on a database a few weeks back (maybe longer, I am kind of busy with several projects right now), and I wanted to truncate a set of tables. There was one central table, and 8 tables related by a FOREIGN KEY constraint. The central table had 6 million rows, and a few of the other tables 20+ million. TRUNCATE is amazingly fast,and when I tried to just use DELETE, it took almost as long as it took me to load the tables. (Exaggeration? A little bit, but not as much as you might imagine, since I use SSIS to BULK LOAD these tables, and the source data is very simple.)

I could just get rid of the constraints, but as a relational db lover, I love constraints. When enforced, they protect me. When not enforced (like when SSIS disables them for a BULK LOAD operation), they are still good documentation. So as any good nerd programmer type would, I started coding a tool to deal with the constraints for me. And as a decent blogger, as soon as it started to get interesting, I realized I could blog about it and upload the code to my website. This in the end makes the code better, because I have to test more, and I learn stuff from readers reminding me things (like a FK script needs to honor CASCADE and NOT FOR REPLICATION, oops.)

I am not going to go over, or even paste, the code in this blog. What I will do is show the interface, and demonstrate how the code works. The interface for the tool is two stored procedures. The first is used to drop the foreign key constraints, storing the scripts for the constraints in a table that it creates named Utility.foreign_key$batch_drop_toRestore:

utility.foreign_key$batch_drop
    @table_schema sysname = '%', --lets you control what schema to drop FKs from
    @table_name sysname = '%',  --lets you control the tables to drop FKs from
    @add_to_history_flag BIT = 0, --by default the procedure creates a table to hold history, this parameter tells it to add to history, if you need to do things incrementally
    @force_replace_status  VARCHAR(20) = 'AS_WAS' --Using the following values: ENABLED, UNTRUSTED, DISABLED, lets you force the status of the constraints, like to quickly turn on the constraint as UNTRUSTED

Then, to recreate the foreign key constraints after you have done your business with the tables:

utility.foreign_key$batch_recreate --if the utility.foreign_key$batch_drop_toRestore table exists, do what is in the table.

Note, If your structures become out of sync with the data in utility.foreign_key$batch_drop_toRestore, you may have to manually apply scripts from the table and/or drop the table. The point of the tool  is to use to drop, do some action, and add the FKs back. The more time passes without applying the script, and things could get out of sync.

To demonstrate their use, here are a few samples usages. The utility uses the code from the previous blog showing how to script a foreign key:  http://sqlblog.com/blogs/louis_davidson/archive/2017/05/24/utility-to-script-a-foreign-key-constraint.aspx and you can download the code for this utility here: https://www.dropbox.com/s/3m9lghtfrnhhxgh/Utility-ForeignKey%24BatchDropRecreate.sql?dl=0

USE TestRebuildConstraints
GO

--Recreate the tables, so we have a known state that can be compared to a script 
DROP TABLE IF EXISTS Demo.ChildTable;
DROP TABLE IF EXISTS Demo.ParentTable;
DROP TABLE IF EXISTS Demo.GrandParentTable;

CREATE TABLE Demo.GrandParentTable
(  
    GrandParentTableId INT NOT NULL
        CONSTRAINT PKGrandParentTable PRIMARY KEY
);

CREATE TABLE Demo.ParentTable
(
    ParentTableId INT NOT NULL
        CONSTRAINT PKParentTable PRIMARY KEY,
    GrandParentTableId INT NULL,
    CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
        FOREIGN KEY (GrandParentTableId)
        REFERENCES Demo.GrandParentTable (GrandParentTableId)
);

CREATE TABLE Demo.ChildTable
(
    ChildTableId INT NOT NULL
        CONSTRAINT PKChildTable PRIMARY KEY,
    ParentTableId INT NULL,
);

--an untrusted constraint
ALTER TABLE Demo.ChildTable WITH NOCHECK
  ADD CONSTRAINT [ChildTable$ref$ParentTable_NotTrusted]
       FOREIGN KEY (ParentTableId) REFERENCES Demo.ParentTable (ParentTableId);

--adding a second constraint. Not typical (or a great idea) but good enough for this exercise
--disabled constraint
ALTER TABLE Demo.ChildTable WITH NOCHECK
    ADD CONSTRAINT [ChildTable$ref$ParentTable_Disabled]
        FOREIGN KEY (ParentTableId) REFERENCES Demo.ParentTable (ParentTableId)
        ON DELETE CASCADE
        ON UPDATE SET NULL;

ALTER TABLE Demo.ChildTable
    NOCHECK CONSTRAINT [ChildTable$ref$ParentTable_Disabled];
GO

This is the set of data to compare against in order to make sure that nothing has changed that we did not want to change.

SELECT is_not_trusted, is_disabled, delete_referential_action_desc, update_referential_action_desc, name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(object_id) = 'Demo'
ORDER BY name;

Which returns:

is_not_trusted is_disabled delete_referential_action_desc update_referential_action_desc  name
-------------- ----------- ------------------------------ ------------------------------- ------------------------------------------
1              1           CASCADE                        SET_NULL                        ChildTable$ref$ParentTable_Disabled
1              0           NO_ACTION                      NO_ACTION                       ChildTable$ref$ParentTable_NotTrusted
0              0           NO_ACTION                      NO_ACTION                       ParentTable$ref$GrandParentTable_Enabled

The basic goal of the procedure is something like the following. I want to run the following set of statements on these tables I have created:

TRUNCATE TABLE Demo.ChildTable;
TRUNCATE TABLE Demo.ParentTable;
TRUNCATE TABLE Demo.GrandParentTable;

Which will fail (even if the constraint is disabled!)

Msg 4712, Level 16, State 1, Line 52
Cannot truncate table 'Demo.ParentTable' because it is being referenced by a FOREIGN KEY constraint.
GO

Now, run the following utility to drop the constraints:

EXEC utility.foreign_key$batch_drop @table_schema = 'Demo';
GO

This creates a table in the utility schema that holds the scripts and object names:

SELECT *
FROM   Utility.foreign_key$batch_drop_toRestore

Now, the TRUNCATE statements will execute.

TRUNCATE TABLE Demo.ChildTable;
TRUNCATE TABLE Demo.ParentTable;
TRUNCATE TABLE Demo.GrandParentTable;
GO

Command(s) completed successfully.

Execute the batch recreate procedure and it will restore the constraints.

EXEC utility.foreign_key$batch_recreate;
GO

Check the foreign key metadata, it should match what you got from the first query of sys.foreign_keys. Next, I will demonstrate two other cases. I will use a TRANSACTION so we can do multiple tests without resetting our structures. In this example, I will show removing constraints one table at a time:

--just remove constraints from ChildTable
EXEC Utility.foreign_key$batch_drop @table_schema = 'Demo', @table_name = 'ChildTable';

--To add more foreign keys to the table, you have to specify the @add_to_history_flag
EXEC Utility.foreign_key$batch_drop @table_schema = 'Demo', @table_name = 'ParentTable';

This causes the following error:

Msg 50000, Level 16, State 1, Procedure foreign_key$batch_drop, Line 32 [Batch Start Line 164]
Parameter @add_to_history_flag set to only allow initialize case

Adding the @add_to_history_flag = 1 parameter value, and it will work:

--This works, adding an additional table
EXEC Utility.foreign_key$batch_drop @table_schema = 'Demo', @table_name = 'ParentTable',
    @add_to_history_flag = 1;

--Now put the constraints back
EXEC utility.foreign_key$batch_recreate;
GO

Finally, you can also change the constraint's enabled status using the @force_replace_status

--the script that is saved off will be for enabled constraints.
EXEC Utility.foreign_key$batch_drop @table_schema = 'Demo',@force_replace_status = 'ENABLED';
GO
EXEC utility.foreign_key$batch_recreate;
GO

Now check the metadata, and you will see the constraints are all trusted:

SELECT is_not_trusted, is_disabled, delete_referential_action_desc, update_referential_action_desc, name
FROM sys.foreign_keys
WHERE OBJECT_SCHEMA_NAME(object_id) = 'Demo'
ORDER BY name;

Which you should now see:

is_not_trusted is_disabled delete_referential_action_desc update_referential_action_desc  name
-------------- ----------- ------------------------------ ------------------------------- ------------------------------------------
0              0           CASCADE                        SET_NULL                        ChildTable$ref$ParentTable_Disabled
0              0           NO_ACTION                      NO_ACTION                       ChildTable$ref$ParentTable_NotTrusted
0              0           NO_ACTION                      NO_ACTION                       ParentTable$ref$GrandParentTable_Enabled

Published Thursday, June 15, 2017 8:39 PM by drsql
Filed under: ,

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

 

Michael R. Schmidt said:

Love this, thanks so much! Very useful!

June 19, 2017 11:24 AM
 

Steve Jazic said:

FK's are the grace/curse of data integrity. Very helpful FK mgmt utilities!

Thank you for sharing them.

June 19, 2017 11:38 AM

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement