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:
- We have both referenced and referencing tables/columns in the mix
- 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)
- 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,
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.
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.