Scripting out composite foreign keys

For a while now, we have been maintaining our production database which feeds thousand of websites by utilizing home grown log shipping process + sync process. Our log shipping process occurs every 15 minutes, and sync process occurs every week. This sync process ensures that all the websites has the most current information, and fix any errors from the past, and expire old products. Before we sync the old database, we truncate/delete all the tables. We use a combination of:

EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
 
EXEC sp_MSForEachTable --'DELETE TABLE ?'
'BEGIN TRY
TRUNCATE TABLE ?
END TRY
BEGIN CATCH
DELETE FROM ?
END CATCH;' 
GO
 
-- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO

Although this disables all the constraint and allows you to delete from tables where foreign key is present, it still does not allow you to truncate the tables (parent tables) until the child table data is deleted. I decided to tweak this process a bit so that we could use the unlogged truncate operation in order to save time and decrease the resources needed.

I found a lot of scripts to script out the foreign keys, but I could not find any which worked on a composite key; because the scripts would provide column names for the same relationships in different rows. So, decided to write a script which will do this. Please freely use this, modify it to your needs, and let me know if there are any bugs so that I can update them.

 
--Build Common Table Expression which can be used multiple times
;WITH table_cte(ForeignKeyName, parentTableName, primaryKeyList, ChildTableName, ForeignKeyList)
AS
(
SELECT DISTINCT
        a.name AS ForeignKeyName 
        , OBJECT_NAME(a.referenced_object_id) AS parentTableName
        , d.name AS PrimaryKeyList 
        , OBJECT_NAME(a.parent_object_id) AS ChildTableName
        , c.name AS ForeignKeyList
 
FROM    sys.foreign_keys a
        JOIN sys.foreign_key_columns b ON a.object_id = b.constraint_object_id
        JOIN sys.COLUMNS c ON b.parent_column_id = c.column_id
                              AND a.parent_object_id = c.object_id
        JOIN sys.COLUMNS d ON b.referenced_column_id = d.column_id
                              AND a.referenced_object_id = d.object_id
 
)
SELECT DISTINCT ForeignKeyName ,
        parentTableName ,
        --primaryKeyList ,
        ChildTableName ,
        --ForeignKeyList,
         PrimaryKeys = LEFT(col.list, LEN(col.list) -1)
        , ForeignKeys = LEFT(ab.list, LEN(ab.list) -1)
INTO #temp
FROM table_cte t
CROSS APPLY
(
       SELECT QuoteNAME(CONVERT(VARCHAR(100), d.Name)) + ', ' AS [text()]
       FROM   sys.foreign_keys a
       JOIN sys.foreign_key_columns b
                  ON a.object_id=b.constraint_object_id
       JOIN sys.COLUMNS c
                  ON b.parent_column_id = c.column_id
                AND a.parent_object_id=c.object_id
       JOIN sys.COLUMNS d
                  ON b.referenced_column_id = d.column_id
               AND a.referenced_object_id = d.object_id
          WHERE t.ForeignKeyName = a.name
          ORDER BY b.constraint_object_id
           FOR XML PATH('')
) AS col(list)
CROSS APPLY
(
 
       SELECT QuoteNAME(CONVERT(VARCHAR(100), c.Name)) + ', ' AS [text()]
       FROM   sys.foreign_keys a
       JOIN sys.foreign_key_columns b
                  ON a.object_id=b.constraint_object_id
       JOIN sys.COLUMNS c
                  ON b.parent_column_id = c.column_id
                AND a.parent_object_id=c.object_id
       JOIN sys.COLUMNS d
                  ON b.referenced_column_id = d.column_id
               AND a.referenced_object_id = d.object_id
          WHERE t.ForeignKeyName = a.name
           ORDER BY b.constraint_object_id
           FOR XML PATH('')
) AS ab(list)
 
SELECT 'ALTER TABLE ' + ChildTableName + ' WITH CHECK ADD CONSTRAINT ' + ForeignKeyName + ' FOREIGN KEY (' + ForeignKeys + ') REFERENCES ' + ParentTableName + ' (' + PrimaryKeys + ')'
,ForeignKeyName ,
        parentTableName ,
        ChildTableName ,
        PrimaryKeys ,
        ForeignKeys
FROM #temp
 
 
--Work completed. Drop table.
DROP TABLE #temp

You can find the drop constraint scripts all throughout. But I could not find one which allows you to script out composite foreign keys.

Please be sure to make a copy of all the constraints above before you go and run the drop statements from below:

1
2
3

select 'ALTER TABLE ' + table_name + ' DROP CONSTRAINT ' + CONSTRAINT_NAME
from INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE CONSTRAINT_TYPE = 'Foreign Key'

Happy T-SQLing!!

Mike Motyl October 13, 2011 at 5:48 pm

EXCELENT SCRIPT!

I ran in to the exact same problem where the scripts I was finding were not handling composite keys. Your script was 95% of what. I just needed to add 3 things to get the other 5%.

1) When I ran the script I was getting an extra comma at the end of each column so I changed:
SELECT QuoteNAME(CONVERT(VARCHAR(100), d.Name)) + ‘, ‘ AS [text()]
TO
SELECT COALESCE(QuoteNAME(CONVERT(VARCHAR(100), d.Name)) + ‘,’,”) AS [text()]

AND

SELECT QuoteNAME(CONVERT(VARCHAR(100), c.Name)) + ‘, ‘ AS [text()]
TO
SELECT COALESCE(QuoteNAME(CONVERT(VARCHAR(100), c.Name)) + ‘,’,”) AS [text()]

2) I took into account [delete_referential_action] and [delete_referential_action_desc] from [sys.foreign_keys] so I could know whether or not to add ‘ON DELETE’ to the ADD statement.

3) I included the schema name in the script

Final product was:

ALTER TABLE [dbo].[ChildTable] WITH CHECK ADD CONSTRAINT FK_Blah FOREIGN KEY ([Column1],[Column2]) REFERENCES [dbo].[ParentTable] ([Column1],[Column2]) ON DELETE CASCADE

In the end, I created a Table-Valued Function that in addition to the ADD statement also generated a DROP statement so I could do something like:

SELECT SchemaName, ParentTable, ChildTable, DropStatement, AddStatement
FROM udfUtility_ScriptForeignKeyAlterStatements()
WHERE ParentTable = ‘Blah’

Thanks for posting your script!

2 thoughts on “Scripting out composite foreign keys”

Subhash Pant May 3, 2011 at 7:10 pm

Had some errors on the QUOTEFIELD. I’ve updated the code. Please let me know if there is any problems.
Mike Motyl October 13, 2011 at 5:48 pm

EXCELENT SCRIPT!

I ran in to the exact same problem where the scripts I was finding were not handling composite keys. Your script was 95% of what. I just needed to add 3 things to get the other 5%.

1) When I ran the script I was getting an extra comma at the end of each column so I changed:
SELECT QuoteNAME(CONVERT(VARCHAR(100), d.Name)) + ‘, ‘ AS [text()]
TO
SELECT COALESCE(QuoteNAME(CONVERT(VARCHAR(100), d.Name)) + ‘,’,”) AS [text()]

AND

SELECT QuoteNAME(CONVERT(VARCHAR(100), c.Name)) + ‘, ‘ AS [text()]
TO
SELECT COALESCE(QuoteNAME(CONVERT(VARCHAR(100), c.Name)) + ‘,’,”) AS [text()]

2) I took into account [delete_referential_action] and [delete_referential_action_desc] from [sys.foreign_keys] so I could know whether or not to add ‘ON DELETE’ to the ADD statement.

3) I included the schema name in the script

Final product was:

ALTER TABLE [dbo].[ChildTable] WITH CHECK ADD CONSTRAINT FK_Blah FOREIGN KEY ([Column1],[Column2]) REFERENCES [dbo].[ParentTable] ([Column1],[Column2]) ON DELETE CASCADE

In the end, I created a Table-Valued Function that in addition to the ADD statement also generated a DROP statement so I could do something like:

SELECT SchemaName, ParentTable, ChildTable, DropStatement, AddStatement
FROM udfUtility_ScriptForeignKeyAlterStatements()
WHERE ParentTable = ‘Blah’

Thanks for posting your script!

Thoughts, codes, occasional rants, many others.

Data.

2 thoughts on “Scripting out composite foreign keys”

Leave a Reply Cancel reply