Skip to Content

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
            &#1
60;       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]
&#160
;        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)

Leave a Comment