How to Drop and Recreate Foreign Key Constraints

The following script provides for a convenient to generate the drop foreign key commands and (more importantly) their corresponding “recreate foreign key” commands which allow you to easily manipulate data across constrained tables.

Care should be using this script on high transaction volume systems where there may be a risk of un-constrained data entry taking place and potentially impacting on the data integrity of the environment – not to mention complications when trying to recreate the FK constraints again.

Just update the @table and @schema variables below, and you should be good to go!

set nocount on
declare @table sysname
declare @schema sysname
select
@table = 'TABLE',
@schema = 'SCHEMA'
print '/*Drop Foreign Key Statements for ['+@schema+'].['+@table+']*/'
select
'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+ o.name+'] DROP CONSTRAINT ['+fk.name+']'
from sys.foreign_keys fk
inner join sys.objects o
on fk.parent_object_id = o.object_id
where 
o.name = @table and
SCHEMA_NAME(o.schema_id)  = @schema
print '/*Create Foreign Key Statements for ['+@schema+'].['+@table+']*/'
select 'ALTER TABLE ['+SCHEMA_NAME(o.schema_id)+'].['+o.name+'] ADD CONSTRAINT ['+fk.name+'] FOREIGN KEY (['+c.name+']) 
REFERENCES ['+SCHEMA_NAME(refob.schema_id)+'].['+refob.name+'](['+refcol.name+'])'
from sys.foreign_key_columns fkc
inner join sys.foreign_keys fk
on fkc.constraint_object_id = fk.object_id
inner join sys.objects o
on fk.parent_object_id = o.object_id
inner join sys.columns c
on      fkc.parent_column_id = c.column_id and
o.object_id = c.object_id
inner join sys.objects refob
on fkc.referenced_object_id = refob.object_id
inner join sys.columns refcol
on fkc.referenced_column_id = refcol.column_id and
fkc.referenced_object_id = refcol.object_id
where 
o.name = @table and
SCHEMA_NAME(o.schema_id)  = @schema