#0324 – SQL Server – Script foreign key creation statements


A long time ago, Pinal Dave, a.k.a SQLAuthority (B|T) wrote a post on Two Methods to Retrieve List of Primary Keys and Foreign Keys of Database at the end of which he asked the readers to come up with queries that would re-create the original definition of the primary and foreign keys.

Scripting primary keys is quite easy, but scripting foreign keys is a challenge because of the following:

  1. We have both referenced and referencing tables/columns in the mix
  2. We may have multiple columns and getting the order of the columns in both – referenced and referencing column lists is important (a column A cannot cross-reference to corresponding column B)
  3. We need to (at least everyone should!) specify the update and delete actions

After many years, I got around to this action item and wrote the following script to re-create the original foreign key definition.

USE AdventureWorks2012
GO
;
WITH    ReferentialKeys ( ConstraintName,
                          ReferencedConstraint,
                          DeleteRule,
                          UpdateRule,
                          ReferencingTableSchema,
                          ReferencingTable,
                          ReferencedTableSchema,
                          ReferencedTable )
          AS (
               SELECT   DISTINCT
                        isrc.CONSTRAINT_NAME AS ConstraintName,
                        isrc.UNIQUE_CONSTRAINT_NAME AS ReferencedConstraint,
                        isrc.DELETE_RULE AS DeleteRule,
                        isrc.UPDATE_RULE AS UpdateRule,
                        ReferencingConstraint.TABLE_SCHEMA AS ReferencingTableSchema,
                        ReferencingConstraint.TABLE_NAME AS ReferencingTable,
                        ReferencedConstraint.TABLE_SCHEMA AS ReferencedTableSchema,
                        ReferencedConstraint.TABLE_NAME AS ReferencedTable
               FROM     INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS AS isrc
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
                                    ON isrc.CONSTRAINT_SCHEMA = ReferencingConstraint.CONSTRAINT_SCHEMA
                                   AND isrc.CONSTRAINT_NAME = ReferencingConstraint.CONSTRAINT_NAME
                        INNER JOIN INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencedConstraint
                                    ON isrc.UNIQUE_CONSTRAINT_SCHEMA = ReferencedConstraint.CONSTRAINT_SCHEMA
                                   AND isrc.UNIQUE_CONSTRAINT_NAME = ReferencedConstraint.CONSTRAINT_NAME
             ) ,
        ReferencingColumns ( ReferencingConstraint,
                             ReferencedConstraint,
                             ReferencingColumn )
          AS (
               SELECT   ReferentialKeys.ConstraintName AS ReferencingConstraint,
                        ReferentialKeys.ReferencedConstraint AS ReferencedConstraint,
                        CAST((
                               SELECT   ( ','
                                          + QUOTENAME(ReferencingConstraint.COLUMN_NAME) )
                               FROM     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencingConstraint
                               WHERE    ReferencingConstraint.CONSTRAINT_NAME = ReferentialKeys.ConstraintName
                               ORDER BY ReferencingConstraint.ORDINAL_POSITION
                             FOR
                               XML PATH('')
                             ) AS NVARCHAR(MAX)) AS ReferencedColumn
               FROM     ReferentialKeys
             ) ,
        ReferencedColumns ( ReferencingConstraint, ReferencedConstraint, ReferencedColumn )
          AS (
               SELECT   ReferentialKeys.ConstraintName AS ReferencingConstraint,
                        ReferentialKeys.ReferencedConstraint AS ReferencedConstraint,
                        CAST((
                               SELECT   ( ','
                                          + QUOTENAME(ReferencedConstraint.COLUMN_NAME) )
                               FROM     INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS ReferencedConstraint
                               WHERE    ReferencedConstraint.CONSTRAINT_NAME = ReferentialKeys.ReferencedConstraint
                               ORDER BY ReferencedConstraint.ORDINAL_POSITION
                             FOR
                               XML PATH('')
                             ) AS NVARCHAR(MAX)) AS ReferencedColumn
               FROM     ReferentialKeys
             )
    SELECT  'IF OBJECT_ID(''' + QUOTENAME(ReferentialKeys.ConstraintName)
            + ''',''F'') IS NOT NULL
    BEGIN
        ALTER TABLE ' + QUOTENAME(ReferentialKeys.ReferencingTableSchema)
            + '.' + QUOTENAME(ReferentialKeys.ReferencingTable)
            + '
            WITH CHECK ADD CONSTRAINT '
            + QUOTENAME(ReferentialKeys.ConstraintName) + ' FOREIGN KEY ('
            + SUBSTRING(ReferencingColumns.ReferencingColumn, 2,LEN(ReferencingColumns.ReferencingColumn))
            + ') REFERENCES '
            + QUOTENAME(ReferentialKeys.ReferencedTableSchema) + '.'
            + QUOTENAME(ReferentialKeys.ReferencedTable) + ' ('
            + SUBSTRING(ReferencedColumns.ReferencedColumn, 2,LEN(ReferencedColumns.ReferencedColumn)) + ')'
            + ' ON DELETE ' + ReferentialKeys.DeleteRule + ' ON UPDATE ' + ReferentialKeys.UpdateRule + ';
    END
GO'
    FROM    ReferentialKeys
            INNER JOIN ReferencingColumns
                    ON ReferentialKeys.ConstraintName = ReferencingColumns.ReferencingConstraint
                   AND ReferentialKeys.ReferencedConstraint = ReferencingColumns.ReferencedConstraint
            INNER JOIN ReferencedColumns
                    ON ReferentialKeys.ConstraintName = ReferencedColumns.ReferencingConstraint
                   AND ReferentialKeys.ReferencedConstraint = ReferencedColumns.ReferencedConstraint
    ORDER BY ReferentialKeys.ReferencingTableSchema,
             ReferentialKeys.ReferencingTable,
             ReferentialKeys.ConstraintName
GO

It’s now time to take this query around for a test drive. Here’s the definition of “FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID” that my query produces (formatted for better readability):

IF OBJECT_ID('[FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]','F') IS NOT NULL
    BEGIN
        ALTER TABLE [Sales].[SalesOrderDetail]
            WITH CHECK ADD CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
                       FOREIGN KEY ([SpecialOfferID],[ProductID])
                       REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID],[ProductID])
                       ON DELETE NO ACTION
                       ON UPDATE NO ACTION;
    END
GO

And here’s the query that SSMS produces when I ask it to script out the key for me.

IF NOT EXISTS (SELECT *
               FROM sys.foreign_keys
               WHERE object_id = OBJECT_ID(N'[Sales].[FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]')
                 AND parent_object_id = OBJECT_ID(N'[Sales].[SalesOrderDetail]'))
    ALTER TABLE [Sales].[SalesOrderDetail]
        WITH CHECK ADD  CONSTRAINT [FK_SalesOrderDetail_SpecialOfferProduct_SpecialOfferIDProductID]
                   FOREIGN KEY([SpecialOfferID], [ProductID])
                   REFERENCES [Sales].[SpecialOfferProduct] ([SpecialOfferID], [ProductID])
GO

Fairly accurate, wouldn’t you say? 🙂

Do share any such useful scripts that you may have in the Scripts page on this site.

Until we meet next time,

Be courteous. Drive responsibly.

Advertisements

2 thoughts on “#0324 – SQL Server – Script foreign key creation statements

  1. rjacarnegie

    This is just what I’m looking for today! I have a specific use for it, to compare many databases which should contain the same key definitions. I’ve got the SQL Compare tool, but it seems to compare two databases only. The complexity of this code tells me that I am very wise to look for the work already done!

    I am interested in a comment here http://www.linkedin.com/groups/0324-SQL-Server-Script-foreign-40572.S.5844109414218346500
    about this work. I agree with Stavros that this script will be improved by dealing with a constraint which is disabled. In my application, that probably would have been done by mistake, and I want to retrieve a statement that represents the wrong state of the constraint, that it is disabled, so that I can detect it.

    However, I think that Stavros’s changes include some simple mistakes (including not qualifying column “is_disabled” with a table alias) and also a fundamental one. I think it’s the case that if you create a constraint “WITH NOCHECK” as he proposes, then the constraint is not disabled, although it also is not trusted, because it is not checked for existing data. It is checked for new data.

    Therefore, Stavros’s change should also include creating the constraint and then immediately disabling the constraint. I think that this is a necessary step to produce a disabled constraint.

    I think don’t need this feature myself, because I will only want to notice and enable a constraint that is disabled, but I think it would be an elegant improvement to the tool, and quite simple to do.

    I will be satisfied to insert ‘/* DISABLED */’ at the start of definition of a foreign key constraint which is disabled!

    A further development could be to have a query with columns for the name of the table to alter, and the name of the constraint, the definition of the constraint itself, its enabled and/or trusted state, and the command to disable or enable it. I haven’t worked it all out, but it may be that the statements generated could include the text ‘/* ENABLED */’ in the statement body, to be substituted by the user with whichever combination of WITH CHECK and NOCHECK is needed for their purpose.

    Like

    Reply
  2. Nakul Vachhrajani

    Hello!

    Thank-you for the feedback and comments around the associated thread on LinkedIn. Yes this script is a base, a starting point and can be customized as per the requirements.

    Like

    Reply

Let me know what you think about this post by leaving your feedback here!

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s