Easy Foreign Key Drop and Restore for SQL Server

From time to time, we’ve all had a database query to run that was stymied by foreign key constraints.

In my case, it was a request to restore a table’s values from one copy of the system to another. We knew that after the information was refreshed, there wouldn’t be any key violations. But SQL server was coded not to trust us, so I couldn’t even run the truncate statement to get rid of the original data.

Of course you want the foreign keys to exist. It’s the best way to prevent buggy code from creating situations where rows that should point to a parent point to nothing at all. If you have an Orders table, and that table has a column with an ID number that points to a Customers table, you can’t let your software delete the Customers record, because then you have Orders for nobody.

But once in a while, the king of the database needs to override the rules in order to get something done.

Unfortunately, there’s no “super delete” function that lets you bypass the constraint.

I decided to write a stored procedure that would save the relevant information about my constraints, drop them, and restore them when I was ready to have them back. I leaned heavily on the work posted by Sushant Saha on DatabaseJournal.com.

I ended up with a stored procedure with usage like this:

The first parameter accepts any number of table names. I’ll warn you now that the way I wrote this means that it won’t work for table names that has spaces in them. (No one does that though, do they?)

The second parameter is ‘D’ to drop constraints or ‘R’ to restore them. That way, I can run the procedure to drop them, do my other work, then flip the D to an R, and run it again to restore.

I can make it this easy because the procedure also leaves behind a table named dbo.FKeys, which displays an easily readable representation of the foreign key contstratints in the database. Columns show the name of the constraint, names of primary and foreign tables, and names of primary and foreign columns.

The final parameter can be flipped to a 1 to display the ALTER TABLE statements that will be applied rather than actually applying them. You would be wise to run it like this before letting it fly from scratch.

Running this with a 1 in the last parameter will also update FKeys, so if you’ve applied an update that may have added foreign key constraints, you can refresh the user-friendly readout of them without chaning anything in your database structure by running it with a 1 at the end.

I took this exercise out to four columns, becase I was sure that none of my primary keys need more than that. It’s easily adapted to more, though, if you have such needs.

The code for the procedure is below.

One would be well advised to be cautious playing with this stuff from scripts copied from other people’s websites, but I hope its either educational or helpful to someone, somewhere down the road.

Posted in Database Design, MS SQL, Transact SQL | Tagged , , , , , , | Leave a comment

Comments are closed.