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 script a FOREIGN KEY Constraint

Note: Amended to include the word include in the first Note (I am a terrible editor of my own writing!) and to fix a bug in the code with a misplaced parenthesis
Note: Amended to include cascading and NOT FOR REPLICATION.

As noted in my previous post, I am in the middle of building a utility (for work, and for my next SQLBLOG post), that will help when you need to drop the foreign key constraints on a table, but then replace them without having to save off the script manually. In that post, I showed how to manually create a foreign key constraint in three possible states.

Next, in order to create the utility to script the FOREIGN KEY constraints before dropping them, I need a way to script the constraint. In this post I present a user defined function that will generate a script that does the scripting of a foreign key constraint. The code is largely based on some code from Aaron Bertrand in this blog (with his permission naturally!) with a few edits to script constraints as enabled, disabled or untrusted if the source constraint was in that condition (or you can force the constraints to a certain way if you so desire as well.)

In this blog entry, I present the code for this function. The code has some comments to illuminate most of what is going on, but I am not going to do too much coverage of the code. Just the code, and some test cases.

utility.foreign_key$script (download the code here)

CREATE DATABASE TestRebuildConstraints;
GO
USE TestRebuildConstraints
GO

IF DB_ID() = DB_ID('TestRebuildConstraints') --helps me not create stuff in master
    EXEC('CREATE SCHEMA utility');
GO

CREATE OR ALTER FUNCTION utility.foreign_key$script(
    @schema_name sysname,
    @foreign_key_name sysname,
    @constraint_status VARCHAR(20) = 'AS_WAS' --ENABLED, UNTRUSTED, DISABLED
                                               --ANY OTHER VALUES RETURN NULL
)
--------------------------------------------------
-- Use to script a foreign key constraint
--
-- 2017  Louis Davidson  drsql.org
--   Thanks to Aaron Bertrand and John Paul Cook's code
--------------------------------------------------
RETURNS NVARCHAR(MAX)
AS
BEGIN
    --based on code to gen list of FK constraints from this article by Aaron Bertrand
    --
https://www.mssqltips.com/sqlservertip/3347/drop-and-recreate-all-foreign-key-constraints-in-sql-server/

    --and code from John Paul Cook:
    --
https://social.technet.microsoft.com/wiki/contents/articles/2958.script-to-create-all-foreign-keys.aspx

    DECLARE @script NVARCHAR(MAX);

    IF @constraint_status NOT IN ('AS_WAS','ENABLED','UNTRUSTED','DISABLED')
        RETURN NULL;

    SELECT @script
        =  N'ALTER TABLE ' + QUOTENAME(cs.name) + '.' + QUOTENAME(ct.name) + CHAR(13) + CHAR(10) + '   '
            --code added to set the constraint's status if it is not to be checked (and
            --in the case of disabled, you create it not trusted and disable it
          + CASE
                WHEN(is_not_trusted = 1
                     OR fk.is_disabled = 1
                      OR @constraint_status IN ( 'UNTRUSTED', 'DISABLED' ))
                    --not forcing it to be enabled
                     AND @constraint_status <> 'ENABLED' THEN
                     'WITH NOCHECK '
                ELSE
                     ''
            END
          + 'ADD CONSTRAINT ' + QUOTENAME(fk.name) + CHAR(13) + CHAR(10) +
          '      FOREIGN KEY ('
          + STUFF((SELECT   ',' + QUOTENAME(c.name)
                    -- get all the columns in the constraint table
                    FROM     sys.columns c
                            INNER JOIN sys.foreign_key_columns fkc
                                 ON fkc.parent_column_id = c.column_id
                                    AND fkc.parent_object_id = c.object_id
                    WHERE    fkc.constraint_object_id = fk.object_id
                    ORDER BY fkc.constraint_column_id
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'),1,1,N'')
           + ')' + CHAR(13) + CHAR(10) + '         REFERENCES ' + QUOTENAME(rs.name) + '.' + QUOTENAME(rt.name)
          + '('
           + STUFF((SELECT   ',' + QUOTENAME(c.name)
                    -- get all the referenced columns
                   FROM     sys.columns c
                            INNER JOIN sys.foreign_key_columns fkc
                                ON fkc.referenced_column_id = c.column_id
                                   AND fkc.referenced_object_id = c.object_id
                   WHERE    fkc.constraint_object_id = fk.object_id
                   ORDER BY fkc.constraint_column_id
        FOR XML PATH(N''), TYPE).value(N'.[1]', N'nvarchar(max)'),1,1, N'') + ')'
         + CASE fk.update_referential_action
                WHEN 1 THEN CHAR(13) + CHAR(10) + '         ON UPDATE CASCADE '
                WHEN 2 THEN CHAR(13) + CHAR(10) + '         ON UPDATE SET NULL '
                 WHEN 3 THEN CHAR(13) + CHAR(10) + '         ON UPDATE SET DEFAULT '
                ELSE '' --could also say "no action" which is the default
           END
          + CASE fk.delete_referential_action
                WHEN 1 THEN CHAR(13) + CHAR(10) + '         ON DELETE CASCADE '
                WHEN 2 THEN CHAR(13) + CHAR(10) + '         ON DELETE SET NULL '
                 WHEN 3 THEN CHAR(13) + CHAR(10) + '         ON DELETE SET DEFAULT '
                ELSE '' --could also say "no action" which is the default
            END
          + CASE fk.is_not_for_replication
                WHEN 1 THEN CHAR(13) + CHAR(10) + '         NOT FOR REPLICATION '
                ELSE ''
             END
          + ';'
          + CASE
                WHEN(fk.is_disabled = 1 AND @constraint_status IN ( 'DISABLED', 'AS_WAS' ))
                     OR @constraint_status = 'DISABLED'
                     THEN CHAR(13) + CHAR(10)+  CHAR(13) + CHAR(10)+   'ALTER TABLE ' + QUOTENAME(cs.name) + '.'
                          + QUOTENAME(ct.name) + CHAR(13) + CHAR(10)
                           + '   NOCHECK CONSTRAINT ' + QUOTENAME(fk.name) + ';'
                 ELSE
                    ''
            END
    FROM   sys.foreign_keys fk
           INNER JOIN sys.tables rt
                -- referenced table
               ON fk.referenced_object_id = rt.object_id
           INNER JOIN sys.schemas rs
                ON rt.schema_id = rs.schema_id
           INNER JOIN sys.tables ct
               -- constraint table
               ON fk.parent_object_id = ct.object_id
           INNER JOIN sys.schemas cs
               ON ct.schema_id = cs.schema_id
    WHERE  OBJECT_SCHEMA_NAME(fk.object_id) = @schema_name
           AND fk.name = @foreign_key_name;
    RETURN @script;
END;

Now, to test the code, I will create a few tables:

--To test, using these tables, I will create three tables (which will anchor the tests of the
--drop and recreate utilities as well.
CREATE SCHEMA Demo;
GO
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);

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

Now, check that the constraints are as expected:

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

is_not_trusted is_disabled name
-------------- ----------- ----------------------------------------------
0              0           ParentTable$ref$GrandParentTable_Enabled
1              0           ChildTable$ref$ParentTable_NotTrusted
1              1           ChildTable$ref$ParentTable_Disabled

Next, I will test the constraints in several different was, sometimes using AS_WAS, and others forcing the different configurations:

SELECT utility.foreign_key$script('Demo','ParentTable$ref$GrandParentTable_Enabled','AS_WAS') AS Original;

Original
------------------------------------------------------------------------
ALTER TABLE [Demo].[ParentTable]
   ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
      FOREIGN KEY ([GrandParentTableId])
         REFERENCES [Demo].[GrandParentTable]([GrandParentTableId]);


SELECT utility.foreign_key$script('Demo','ChildTable$ref$ParentTable_NotTrusted','AS_WAS') AS Untrusted;

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

SELECT utility.foreign_key$script('Demo','ChildTable$ref$ParentTable_Disabled','AS_WAS') AS Original;


Original
------------------------------------------------------------------------
ALTER TABLE [Demo].[ChildTable]
   WITH NOCHECK ADD CONSTRAINT [ChildTable$ref$ParentTable_Disabled]
      FOREIGN KEY ([ParentTableId])
         REFERENCES [Demo].[ParentTable]([ParentTableId]);

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

SELECT utility.foreign_key$script('Demo','ChildTable$ref$ParentTable_Disabled','AS_WERS') AS Fails;

Fails
------------------------------------------------------------------------
NULL

Untrusted
------------------------------------------------------------------------
ALTER TABLE [Demo].[ParentTable]
   WITH NOCHECK ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
      FOREIGN KEY ([GrandParentTableId])
         REFERENCES [Demo].[GrandParentTable]([GrandParentTableId]);

SELECT utility.foreign_key$script('Demo','ParentTable$ref$GrandParentTable_Enabled','DISABLED') AS Disabled;

Disabled
------------------------------------------------------------------------
ALTER TABLE [Demo].[ParentTable]
   WITH NOCHECK ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
      FOREIGN KEY ([GrandParentTableId])
         REFERENCES [Demo].[GrandParentTable]([GrandParentTableId]);

ALTER TABLE [Demo].[ParentTable]
   NOCHECK CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled];

--Now we test cascade and not for replication

--First UPDATE and DELETE cascading

ALTER TABLE [Demo].[ParentTable]
   DROP CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]

ALTER TABLE [Demo].[ParentTable]
   ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
      FOREIGN KEY ([GrandParentTableId])
          REFERENCES [Demo].[GrandParentTable]([GrandParentTableId])
          ON UPDATE CASCADE
         ON DELETE SET DEFAULT;

SELECT utility.foreign_key$script('Demo','ParentTable$ref$GrandParentTable_Enabled','AS_WAS') AS CascadeTest;

CascadeTest
--------------------------------------------------------------------------------------
ALTER TABLE [Demo].[ParentTable]
   ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
      FOREIGN KEY ([GrandParentTableId])
         REFERENCES [Demo].[GrandParentTable]([GrandParentTableId]
         ON UPDATE CASCADE
          ON DELETE SET DEFAULT );


--Next Add Not For Replication
ALTER TABLE [Demo].[ParentTable]
   DROP CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]

ALTER TABLE [Demo].[ParentTable]
    ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
       FOREIGN KEY ([GrandParentTableId])
         REFERENCES [Demo].[GrandParentTable]([GrandParentTableId])
         ON UPDATE CASCADE
         ON DELETE SET DEFAULT
         NOT FOR REPLICATION;

SELECT utility.foreign_key$script('Demo','ParentTable$ref$GrandParentTable_Enabled','AS_WAS') AS CascadeNotForRepTest;

Note that NOT FOR REPLICATION makes the constraint not trusted

CascadeNotForRepTest
------------------------------------------------------------------------------------------
ALTER TABLE [Demo].[ParentTable]
   WITH NOCHECK ADD CONSTRAINT [ParentTable$ref$GrandParentTable_Enabled]
      FOREIGN KEY ([GrandParentTableId])
         REFERENCES [Demo].[GrandParentTable]([GrandParentTableId]
         ON UPDATE CASCADE
         ON DELETE SET DEFAULT
         NOT FOR REPLICATION );

Finally, to make sure that the code does deal with composite key references (which I didn't really doubt, but hey, you really need to test this stuff right?

CREATE TABLE Demo.MultiKey
(
    Column1 INT NOT NULL,
    Column2 INT NOT NULL,
    PRIMARY KEY (Column1, Column2)
)
CREATE TABLE Demo.MultiKeyRef
(
    Column1 INT NOT NULL,
    Column2 INT NOT NULL,
    FOREIGN KEY (Column1, Column2) REFERENCES Demo.MultiKey (Column1, Column2) --no name for key here
)
   
First, need to look up the name to get the system generated name:

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

Now, execute the three different ways we can script:

SELECT utility.foreign_key$script('Demo','FK__MultiKeyRef__31EC6D26','AS_WAS') AS Original;
SELECT utility.foreign_key$script('Demo','FK__MultiKeyRef__31EC6D26','DISABLED') AS Disabled;
SELECT utility.foreign_key$script('Demo','FK__MultiKeyRef__31EC6D26','UNTRUSTED') AS Untrusted;

Original
------------------------------------------------------------------
ALTER TABLE [Demo].[MultiKeyRef]
   ADD CONSTRAINT [FK__MultiKeyRef__31EC6D26]
      FOREIGN KEY ([Column1],[Column2])
         REFERENCES [Demo].[MultiKey]([Column1],[Column2]);

Disabled
------------------------------------------------------------------
ALTER TABLE [Demo].[MultiKeyRef]
   WITH NOCHECK ADD CONSTRAINT [FK__MultiKeyRef__31EC6D26]
      FOREIGN KEY ([Column1],[Column2])
         REFERENCES [Demo].[MultiKey]([Column1],[Column2]);

ALTER TABLE [Demo].[MultiKeyRef]
   NOCHECK CONSTRAINT [FK__MultiKeyRef__31EC6D26];

Untrusted
------------------------------------------------------------------
ALTER TABLE [Demo].[MultiKeyRef]
   WITH NOCHECK ADD CONSTRAINT [FK__MultiKeyRef__31EC6D26]
      FOREIGN KEY ([Column1],[Column2])
         REFERENCES [Demo].[MultiKey]([Column1],[Column2]);

Hopefully this script can be of some use to you, I will use it in the next blog where I build the utility to drop and recreate FOREIGN KEY constraints.

(Note: This works with memory optimized tables as well, since the syntax is the same)

Published Wednesday, May 24, 2017 10:50 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

No Comments

Leave a Comment

(required) 
(required) 
Submit

This Blog

Syndication

Links to my other sites

Archives

Privacy Statement