Technical: Microsoft – SQL Server – Constraint – Foreign Key – Enable/Disable

Technical: Microsoft – SQL Server – Constraint – Foreign Key – Enable/Disable

Introduction

When you are trying to bulk-load data from a staging table into your main table,
you might experience foreign key constraints getting in your way.

In this particular case, we have data in sub-tables referencing our table of interests.

And, so I really need to ‘blind’ those relationships, replace the data, and re-enable our blind spots

Code

To use pass in your parent schema and table name.



if OBJECT_ID('[dbo].[sp_DisableForeignKeyDependants]') is null
begin
	exec ('Create PROCEDURE [dbo].[sp_DisableForeignKeyDependants] as select 1/0 as shell')
end	
go

ALTER PROCEDURE [dbo].[sp_DisableForeignKeyDependants]
      @schemaName nvarchar(255) 
    , @objectName nvarchar(255) 
    , @action     tinyint = 0
    , @scriptOnly  bit = 0
    , @debug bit = 0

AS
BEGIN

/*
** Name: [dbo].[sp_DisableForeignKeyDependants]
**
** Database: ProductionSystem
**
** Purpose:  Disable FK
**
**
** When         Who             What
** --------     --------------  ---------------------------------------------------------------------------------------------------------------
** 07/25/13		Daniel Adeniji 
*/

    SET NOCOUNT ON;

    declare @sql nvarchar(4000)
    declare @sql2 nvarchar(4000)    
    declare @log nvarchar(600)

    declare @ASCII_Tab nvarchar(30)
    declare @ASCII_NewLine nvarchar(30)

    declare @objectIDReference int		
    declare @objectFullName sysname
    declare @foreignKeyName sysname
    declare @schemaNameReferer sysname
    declare @objectNameReferer sysname
    declare @is_disabled	bit
    declare @is_not_trusted bit
    declare @SQLDisable nvarchar(4000)
    declare @SQLEnable nvarchar(4000)

    declare @foreignKey TABLE
	(

		  [id]                  int     not null identity(1,1)
		, [objectIDParent]  int		not null
		, [schemaNameReferenced] sysname not null		
		, [objectNameReferenced]	sysname not null
		, [objectIDConstraint]	int     not null
		, [foreignKeyName]      sysname not null					  		
		, [schemaNameReferer] sysname not null		
		, [objectNameReferer]	sysname not null
		, [is_disabled]			bit     not null
		, [is_not_trusted]		bit     not null

		, SQLDisable as
		     'ALTER TABLE ' 
		    + QuoteName([schemaNameReferenced]) + '.' 
                    + QuoteName([objectNameReferenced])
		    + ' NOCHECK CONSTRAINT ' + QuoteName([foreignKeyName])

		, SQLEnable as
		    'ALTER TABLE ' 
		  + QuoteName([schemaNameReferenced]) + '.'
                  + QuoteName([objectNameReferenced])
		  + ' WITH CHECK CHECK CONSTRAINT ' + QuoteName([foreignKeyName])

	)

	declare @id int
	declare @idMax int

	set @ASCII_Tab = CHAR(9)
	set @ASCII_NewLine = CHAR(13) + CHAR(10)

	set @objectFullName = QUOTENAME(@schemaName) + '.' + QUOTENAME(@objectName)

	set @objectIDReference = OBJECT_ID(@objectFullName)

	insert into @foreignKey
	(
		  [objectIDParent]
		, [schemaNameReferenced]
		, [objectNameReferenced]
		, [objectIDConstraint]		  
		, [foreignKeyName]
		, [schemaNameReferer]
		, [objectNameReferer]
		, [is_disabled]
		, [is_not_trusted]
	)
	select 	  
			 tblFK.parent_object_id --@objectIDReferenced
		   , SCHEMA_NAME(tblParent.schema_id)	 
		   , Object_name(tblParent.object_id)	 		   
		   , tblFK.object_id	 
		   , tblFK.name
		   , schema_name(tblReference.schema_id)
		   , OBJECT_NAME(tblReference.object_id)
		   , tblFK.is_disabled
		   , tblFK.is_not_trusted

	from sys.foreign_keys as tblFK

		inner join sys.tables as tblParent 

			on tblFK.parent_object_id = tblParent.object_id

		inner join sys.tables tblReference

			on tblFK.referenced_object_id = tblReference.object_id

	where 
			(
				(tblReference.object_id = @objectIDReference)
			)				

	if (@action =0)
	begin

		select *
		from   @foreignKey
		order by
		          [schemaNameReferenced]
			, [objectNameReferenced]
			, [schemaNameReferer]
			, [objectNameReferer]

	end		

	set @id = 1
	set @idMax = ( select COUNT(*) from @foreignKey)

	if (@debug = 1)
	begin

		set @log = @ASCII_TAB + @ASCII_TAB 
				+ 'IdMax : ' + cast(@idMax as varchar(30))
		print @log

	end

	if (@action in (1,2))
	begin

	   while (@id <= @idMax)
	   begin

		select 
			  @foreignKeyName = [foreignKeyName]
			, @schemaNameReferer = schemaNameReferer
			, @objectNameReferer = objectNameReferer
			, @SQLDisable = tblFK.SQLDisable
			, @SQLEnable = tblFK.SQLEnable						
			, @is_disabled = is_disabled
			, @is_not_trusted = is_not_trusted

		from @foreignKey tblFK

		where  [id] = @id

		--print 'FK : ' + @foreignKeyName

		if (@action =1)
		begin

		   set @log = @ASCII_Tab + @ASCII_Tab + @SQLDisable
		   print @log

		  if (@scriptOnly =0)
		  begin				
		    exec (@SQLDisable)
		  end						

		end

		else if (@action =2)
		begin

		   set @log = @ASCII_Tab + @ASCII_Tab + @SQLEnable
		   print @log

		   if (@scriptOnly =0)
		   begin				
		      exec (@SQLEnable)
		   end						

	       end

	       set @id = @id + 1

	end	

   end

END
go

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s