{"id":244,"date":"2014-10-20T08:30:57","date_gmt":"2014-10-20T07:30:57","guid":{"rendered":"http:\/\/www.codereview.co\/?p=244"},"modified":"2014-10-15T15:04:15","modified_gmt":"2014-10-15T14:04:15","slug":"how-to-drop-and-recreate-sql-server-foreign-keys","status":"publish","type":"post","link":"http:\/\/www.codereview.co\/index.php\/sql-server-tutorials\/how-to-drop-and-recreate-sql-server-foreign-keys\/","title":{"rendered":"How to drop and recreate SQL Server foreign keys?"},"content":{"rendered":"

Scenario<\/strong><\/span><\/p>\n

You may have been in a scenario where you needed to quickly generate a script to drop and then subsequently re-create all of the foreign keys in a database (or in a specific schema, or matching a specific naming scheme). In some situations you can simply disable and re-enable the constraints, which isn’t all that complex at all. In other cases (say, you want to truncate all tables), you actually need to drop and re-create the constraints.<\/span><\/p>\n

Regardless of the underlying purpose, this is rather tedious to do in Management Studio, since there is no top-level “Foreign Keys” node in the Object Explorer tree – otherwise you could just select multiple items in Object Explorer Details, right-click, and be on your way.<\/span><\/p>\n

When you start thinking about how to solve this problem, and there are existing tips that do offer solutions already, your first thought is probably: “I’ll just use a cursor against sys.foreign_keys and build the scripts dynamically!” Then you realize that some of your foreign key constraints are comprised of more than one column – certainly an often and understandably unforeseen complication. This definitely throws a wrench in your plans, as now it’s a nested cursor: one to loop through all the constraints, and then for each constraint, a loop for the 1-n columns referenced.<\/span><\/p>\n

\u00a0<\/strong><\/span><\/p>\n

Solution<\/strong><\/span><\/p>\n

I have what I think is a better way than trying to write convoluted and nested cursors, and no, it doesn’t involve PowerShell. (That’s not saying PowerShell is a bad approach for this kind of problem, and I invite you to share your solutions from that angle. I’m just trying to stay within the database here.)<\/span><\/p>\n

I’ve recently blogged about the FOR XML PATH() approach to grouped concatenation but I didn’t really get into any real, practical solutions, like this one, in those posts.<\/span><\/p>\n

I have grown quite fond of using this method to solve problems like this, where I can eliminate tedious and repetitive cursor code and\/or while loops. Note that this shift is not in the name of performance – after all, in most cases, it is unimportant whether this specific task is accomplished in 8.7 seconds or 11.2 seconds. It doesn’t end up being any simpler either, really, but it sure is less boring to come up with a working solution that covers all edge cases.<\/span><\/p>\n

The code below generates two separate sets of commands: one to drop all foreign key constraints, and one to create them again. These scripts are stored in a table so that, if you drop the constraints and then disaster of some kind strikes during the create, you still have everything handy and can troubleshoot if needed – including extracting the scripts for all the constraints that haven’t yet run, but aren’t causing any issues otherwise.<\/span><\/p>\n

CREATE TABLE #x — feel free to use a permanent table
\n(
\ndrop_script NVARCHAR(MAX),
\ncreate_script NVARCHAR(MAX)
\n);<\/em><\/span><\/p>\n

DECLARE @drop\u00a0\u00a0 NVARCHAR(MAX) = N”,
\n@create NVARCHAR(MAX) = N”;<\/p>\n

— drop is easy, just build a simple concatenated list from sys.foreign_keys:
\nSELECT @drop += N’
\nALTER TABLE ‘ + QUOTENAME(cs.name) + ‘.’ + QUOTENAME(ct.name)
\n+ ‘ DROP CONSTRAINT ‘ + QUOTENAME(fk.name) + ‘;’
\nFROM sys.foreign_keys AS fk
\nINNER JOIN sys.tables AS ct
\nON fk.parent_object_id = ct.[object_id]
\nINNER JOIN sys.schemas AS cs
\nON ct.[schema_id] = cs.[schema_id];<\/p>\n

INSERT #x(drop_script) SELECT @drop;<\/p>\n

— create is a little more complex. We need to generate the list of
\n— columns on both sides of the constraint, even though in most cases
\n— there is only one column.
\nSELECT @create += N’
\nALTER TABLE ‘
\n+ QUOTENAME(cs.name) + ‘.’ + QUOTENAME(ct.name)
\n+ ‘ ADD CONSTRAINT ‘ + QUOTENAME(fk.name)
\n+ ‘ FOREIGN KEY (‘ + STUFF((SELECT ‘,’ + QUOTENAME(c.name)
\n— get all the columns in the constraint table
\nFROM sys.columns AS c
\nINNER JOIN sys.foreign_key_columns AS fkc
\nON fkc.parent_column_id = c.column_id
\nAND fkc.parent_object_id = c.[object_id]
\nWHERE fkc.constraint_object_id = fk.[object_id]
\nORDER BY fkc.constraint_column_id
\nFOR XML PATH(N”), TYPE).value(N’.[1]’, N’nvarchar(max)’), 1, 1, N”)
\n+ ‘) REFERENCES ‘ + QUOTENAME(rs.name) + ‘.’ + QUOTENAME(rt.name)
\n+ ‘(‘ + STUFF((SELECT ‘,’ + QUOTENAME(c.name)
\n— get all the referenced columns
\nFROM sys.columns AS c
\nINNER JOIN sys.foreign_key_columns AS fkc
\nON fkc.referenced_column_id = c.column_id
\nAND fkc.referenced_object_id = c.[object_id]
\nWHERE fkc.constraint_object_id = fk.[object_id]
\nORDER BY fkc.constraint_column_id
\nFOR XML PATH(N”), TYPE).value(N’.[1]’, N’nvarchar(max)’), 1, 1, N”) + ‘);’
\nFROM sys.foreign_keys AS fk
\nINNER JOIN sys.tables AS rt — referenced table
\nON fk.referenced_object_id = rt.[object_id]
\nINNER JOIN sys.schemas AS rs
\nON rt.[schema_id] = rs.[schema_id]
\nINNER JOIN sys.tables AS ct — constraint table
\nON fk.parent_object_id = ct.[object_id]
\nINNER JOIN sys.schemas AS cs
\nON ct.[schema_id] = cs.[schema_id]
\nWHERE rt.is_ms_shipped = 0 AND ct.is_ms_shipped = 0;<\/p>\n

UPDATE #x SET create_script = @create;<\/p>\n

PRINT @drop;
\nPRINT @create;<\/p>\n

\/*
\nEXEC sp_executesql @drop
\n— clear out data etc. here
\nEXEC sp_executesql @create;
\n*\/<\/p>\n

Conclusion<\/em><\/span><\/h2>\n
I will be the first to admit: the script is a lot to digest. However, before trying to completely reverse engineer all of the logic on first glance, I urge you to try this code (with the EXEC lines still commented out of course) in your hairiest, most complex schemas. Please let me know if you have a scenario where you find any discrepancies in the comments section below.<\/span><\/p>\n
<\/p>\n
Thanks for reading this article,<\/strong><\/span><\/p>\n
Next steps :<\/strong><\/span><\/p>\n
\n
Add this script to your database toolkit<\/strong><\/span><\/li>\n
Share this with your colleagues because Sharing\u00a0 is Learning<\/strong><\/span><\/li>\n
Comment below if you need any assistance<\/strong><\/span><\/li>\n<\/ol>\n
<\/p>\n","protected":false},"excerpt":{"rendered":"
Scenario You may have been in a scenario where you needed to quickly generate a script to drop and then subsequently re-create all of the foreign keys in a database (or in a specific schema, or matching a specific naming scheme). In some situations you can simply disable and re-enable the constraints, which isn’t all […]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[32,3],"tags":[44,41,42,45,13,12],"_links":{"self":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/244"}],"collection":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/comments?post=244"}],"version-history":[{"count":2,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/244\/revisions"}],"predecessor-version":[{"id":374,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/posts\/244\/revisions\/374"}],"wp:attachment":[{"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/media?parent=244"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/categories?post=244"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/www.codereview.co\/index.php\/wp-json\/wp\/v2\/tags?post=244"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}