Drop and Re-Create All Foreign Key Constraints in SQL Server
※ Download: Drop foreign key sql server
If you don't want that, you could write WITH NOCHECK. Not really sure what all it does to add it but if yuo want to know setup a dummy table in a test enviroment and make the change while running Profiler to get a trace of the calls.
The referenced table is called the parent table while the table with the foreign key is called the child table. All comments are reviewed, so stay on subject or we may delete your comment. I'm running SQL Server 2012.
Drop and Re-Create All Foreign Key Constraints in SQL Server - END TRY BEGIN CATCH PRINT ''Truncation failed on''+? You have the options of NO ACTION, CASCADE, SET NULL, or SET DEFAULT.
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. 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. Solution 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. I've recently blogged about the FOR XML PATH approach to grouped concatenation see and , but I didn't really get into any real, practical solutions, like this one, in those posts. 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. 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. 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. We need to generate the list of -- columns on both sides of the constraint, even though in most cases -- there is only one column. 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. All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. I have read the and understand I may unsubscribe at any time. Management Studio just doesn't have a direct way to prove it to you, since it truncates output whether you use print or select, results to text or results to grid, etc. Thanks Aaron for the great script! We need to generate the list of -- columns on both sides of the constraint, even though in most cases -- there is only one column. EF should really have nothing to do with it. Can you show a minimal repro where FK constraints are defined within SQL Server and the above script misses them? Thursday, August 23, 2018 - 12:56:53 PM - Jun The idea is sound, but the script above misses about half of the FKs on EF-generated databases with custom schemas. I'll figure some other way. Let me know if you are interested in improving the script above - I can show you cases where it doesn't find the FK that's already in. Saturday, June 09, 2018 - 9:14:37 AM - Hove I use it in a way where I generate the create script for all tables through SSMS, SSMS first generates create tables and after, all constraints, everything grouped and I write the simple script to delete all foreign key constraints. Very little hustle and no errors. Friday, April 20, 2018 - 6:15:37 PM - Ibrahim Thanks for doing the Grunt Work!! It's an excellant solution. Wednesday, April 04, 2018 - 3:34:47 AM - Michel Manias Thanks Aaron this TSQL is very good, If I may, I would have added the IF EXISTS clause just after the DROP CONSTRAINT like that we can run it many times. Thursday, December 14, 2017 - 3:44:40 AM - Juozas Hi, Foreign key script: its better to use char 10 + char 13 instead of char 13. Thursday, September 14, 2017 - 8:24:13 AM - Kleidi Kumbaro Based on Gauthier Segay code and the comments of other users like me here is a modified script that will include the referentail integrity options on update and on delete as well as with check and not for replication. Keep in mind that this is tested by my only for foreign keys and not on constraints. ON UPDATE CASCADE, ON DELETE SET NULL, etc.. Thursday, May 18, 2017 - 10:36:38 PM - Michael Loved this script! Thursday, April 27, 2017 - 3:01:00 PM - Chris Prosser This saved me a ton of time. Tuesday, April 18, 2017 - 12:05:24 PM - Janelle Great article. Saved me a ton of time. I turned the code into a function so I could the function and return the 2 scripts for a specific table. It returns them for fk constraints on the table and for fk constraints referencing the table. Saved me a lot of work today. Thanks Steve Thursday, August 20, 2015 - 12:21:19 PM - Mitch Kirsch Hi Aaron, great script! The only modification I had to made was to add WHERE ct. Tuesday, March 24, 2015 - 11:33:57 PM - kakugiki This is very helpful! Thursday, March 12, 2015 - 8:55:24 AM - Ramesh Aaron, This article was very helpful. Friday, February 27, 2015 - 8:27:33 AM - Gauthier Segay Aaron and Carl, thanks so much for your contribution. I figured out that we are still missing unique constraints, so I went on and added that and also refactored the whole thing to make it tidy and more maintainable, using CTE preparing all relevant info in a nice format so that don't mix the string building logic with how to obtain the data from system tables. The statement returns a table with a script type for the type of constraint , a create script column and a drop script column. That is just there for spot checking, and isn't what you should be using to execute the commands. E98 Wednesday, January 14, 2015 - 4:04:43 AM - yeti Unfortunately your script only generated create script for 25 out of my 75 foreign keys. The below one generated all of them. Don't know the difference and now have no time to dig into it, just letting you know. Thanks, Sumit Friday, October 17, 2014 - 2:15:28 PM - Aaron Bertrand Yep, got it, this was a starter script that didn't consider cases where FKs may be explicitly defined for replication, have cascade options, or be disabled. The reader should be aware that this only deals with the most straightforward case. Perhaps I will write a follow-up tip that handles a lot more scenarios... Friday, October 17, 2014 - 10:10:01 AM - John Q Martin Great script Aaron, one thing that has tripped me up in the past when doing this is that for some reason people will insist on disabling foreign keys in systems resulting in inconsistencies in the data that will cause the creates to fail. Just something to bear in mind when performing an operation such as this. Friday, October 17, 2014 - 2:53:51 AM - alzdba Nice case, Aaron! Great starting script with a nice use of the For XML path clause. These options can be pulled from the used sys. Cheers, Johan Monday, October 13, 2014 - 1:51:32 PM - Carl Like others I have been meaning to do this for ages, in order to change the collation across a database. If you've worked out how it works it should be pretty obvious where to put them! I was about to sit down and try to create something similar either tonight or tomorrow. Now I don't have to. Keep up the good work. Tuesday, October 07, 2014 - 10:29:37 AM - Aaron Bertrand Thanks James, Yes, I certainly wasn't exhaustive there, thanks for bringing it up. Hopefully these will be trivial additions for the reader... Aaron Tuesday, October 07, 2014 - 8:01:10 AM - James Lean Nice script Aaron, I remember having to generate these scripts in SQL 2000, where FOR XML PATH wasn't an option! One thing you might want to add is to retain any ON CASCADE options that may have been set, plus include the NOT FOR REPLICATION option where applicable. These should be fairly easy to tack on the end of the create script, based on the columns in sys.
SET NOCOUNT ON DECLARE table TABLE RowId INT PRIMARY KEY IDENTITY 1, 1ForeignKeyConstraintName NVARCHAR 200ForeignKeyConstraintTableSchema NVARCHAR 200ForeignKeyConstraintTableName NVARCHAR 200ForeignKeyConstraintColumnName NVARCHAR 200PrimaryKeyConstraintName NVARCHAR 200PrimaryKeyConstraintTableSchema NVARCHAR 200PrimaryKeyConstraintTableName NVARCHAR 200PrimaryKeyConstraintColumnName NVARCHAR 200UpdateRule NVARCHAR 100DeleteRule NVARCHAR 100 INSERT INTO table ForeignKeyConstraintName, ForeignKeyConstraintTableSchema, ForeignKeyConstraintTableName, ForeignKeyConstraintColumnName SELECT U. This script considers both, multiple schemas and multiple columns per foreign key. Keep up the good work. As you mentioned it comes handy when dropping all the data in the database and repopulating it say in test environment. Msg 3727, Level 16, State 0, Line 1 Could not drop constraint. These options can be pulled from the used sys. Once completed, if you need to check the status, use this script to list the constraint status. Drop foreign key sql server thing you might want to add is to retain any ON CASCADE options that may have been set, plus include the NOT FOR REPLICATION option where applicable.
