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:
|
1 2 3 |
qrym_DropOrRestoreForeignKeys, 'Table1, Table2', 'D', 0 |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 |
create procedure dbo.qrym_DropOrRestoreForeignKeys @TableList varchar(255) = null, -- leave null, and it will do all of them in the database. This won't work if there are table names with spaces in them. @DropRestoreFlag char(1) = 'R', -- 'D' = Drop all foreign key constraints for one or more tables -- 'R' = Retstore all foreign key constraints for one or more tables @TestOnly bit = 0 -- set to 1 to just see the statements that would be applied. This will still update the FKeys table when 0. as begin if isnull(@DropRestoreFlag, ' ') not in ('D','R') begin raiserror 50001 '@DropRestoreFlag parameter must be either ''D'' or ''R''' return end /* create/populate table to store all of the foreign key constraints in the system */ if not exists (select 1 from sysobjects where type='u' and uid=1 and name = 'FKeys') CREATE table dbo.FKeys (foreign_key_name varchar(255), keycnt smallint, foreign_table varchar(255), foreign_column_1 varchar(255), foreign_column_2 varchar(255), foreign_column_3 varchar(255), foreign_column_4 varchar(255), primary_table varchar(255), primary_column_1 varchar(255), primary_column_2 varchar(255), primary_column_3 varchar(255), primary_column_4 varchar(255), WhenDropped datetime, WhoDropped varchar(255), WhenRestored datetime, WhoRestored varchar(255), primary key (foreign_key_name)) insert FKeys select cast(f.name as varchar(255)) as foreign_key_name , r.keycnt , cast(c.name as varchar(255)) as foreign_table , cast(fc.name as varchar(255)) as foreign_column_1 , cast(fc2.name as varchar(255)) as foreign_column_2 , cast(fc3.name as varchar(255)) as foreign_column_3 , cast(fc4.name as varchar(255)) as foreign_column_4 , cast(p.name as varchar(255)) as primary_table , cast(rc.name as varchar(255)) as primary_column_1 , cast(rc2.name as varchar(255)) as primary_column_2 , cast(rc3.name as varchar(255)) as primary_column_3 , cast(rc4.name as varchar(255)) as primary_column_4 , null , null , null , null from sysobjects f inner join sysobjects c on f.parent_obj = c.id inner join sysreferences r on f.id = r.constid inner join sysobjects p on r.rkeyid = p.id inner join syscolumns rc on r.rkeyid = rc.id and r.rkey1 = rc.colid inner join syscolumns fc on r.fkeyid = fc.id and r.fkey1 = fc.colid left join syscolumns rc2 on r.rkeyid = rc2.id and r.rkey2 = rc2.colid left join syscolumns fc2 on r.fkeyid = fc2.id and r.fkey2 = fc2.colid left join syscolumns rc3 on r.rkeyid = rc3.id and r.rkey3 = rc3.colid left join syscolumns fc3 on r.fkeyid = fc3.id and r.fkey3 = fc3.colid left join syscolumns rc4 on r.rkeyid = rc4.id and r.rkey4 = rc4.colid left join syscolumns fc4 on r.fkeyid = fc4.id and r.fkey4 = fc4.colid left outer join FKeys fk on f.name = fk.foreign_key_name where f.type = 'F' and fk.foreign_key_name is null /* intermediate variables for the add/drop loop */ create table #t ( foreign_key_name varchar(255), keycnt smallint, foreign_table varchar(255), foreign_column_1 varchar(255), foreign_column_2 varchar(255), foreign_column_3 varchar(255), foreign_column_4 varchar(255), primary_table varchar(255), primary_column_1 varchar(255), primary_column_2 varchar(255), primary_column_3 varchar(255), primary_column_4 varchar(255)) declare @Condition varchar(2000) , @tempTableList varchar(255) , @currTableList varchar(255) , @Query varchar(4000) , @FKName varchar(255) if rtrim(ltrim(isnull(@TableList, ''))) = '' select @Condition = '' else select @Condition = ' where primary_table in (''' + replace(replace(isnull(@TableList, ''), ',', ''','''), ' ', '') + ''')' --select 'insert #t select foreign_key_name, keycnt, foreign_table, foreign_column_1, foreign_column_2, foreign_column_3, foreign_column_4, primary_table, -- primary_column_1, primary_column_2, primary_column_3, primary_column_4 from FKeys' + @Condition select @Query = 'insert #t select foreign_key_name, keycnt, foreign_table, foreign_column_1, foreign_column_2, foreign_column_3, foreign_column_4, primary_table, primary_column_1, primary_column_2, primary_column_3, primary_column_4 from FKeys' + @Condition --select @Query exec (@Query) while (select count(1) from #t) > 0 begin /* drop foreign key constraints */ if @DropRestoreFlag = 'D' begin select top 1 @Query = 'ALTER TABLE ' + foreign_table + ' DROP CONSTRAINT ' + foreign_key_name , @FKName = foreign_key_name from #t order by foreign_table if @TestOnly = 1 begin select 'update FKeys set WhenDropped = getdate(), WhoDropped = suser_sname() where foreign_key_name = ' + @FKName select @Query end else begin update FKeys set WhenDropped = getdate(), WhoDropped = suser_sname() where foreign_key_name = @FKName exec(@Query) end delete #t where foreign_key_name = @FKName end /* restore foreign key restraints */ else begin select top 1 @Query = 'ALTER TABLE ' + foreign_table + ' ADD CONSTRAINT ' + foreign_key_name + ' FOREIGN KEY (' + foreign_column_1 + ') REFERENCES ' + primary_table + ' (' + primary_column_1 + ')' , @FKName = foreign_key_name from #t order by foreign_table if exists (select 1 from sysobjects where name = @FKName and type='F') begin print @FKName + ' already exists. Skipping the restore for this constraint.' end else if @TestOnly = 1 begin select 'update FKeys set WhenRestored = getdate(), WhoRestored = suser_sname() where foreign_key_name = ' + @FKName select @Query end else begin update FKeys set WhenRestored = getdate(), WhoRestored = suser_sname() where foreign_key_name = @FKName exec(@Query) end delete #t where foreign_key_name = @FKName end end end |
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.