Home > SQL Server, T-SQL > Temporarily Disabling Referential Integrity Constraints on a SQL Table

Temporarily Disabling Referential Integrity Constraints on a SQL Table

I recently came across a situation where part of the data processing would put the data temporarily into a non-referential state. In order to accommodate this, without scripting Drop/Create statements which could get out of sync with other development work, I put together the following stored procedure which enables/disables check constraints.

Feel free to use/copy/amend as appropriate, but please let me have your comments/updates.

Create Procedure [etl].[SetCheckContraintsState]
    Enables/Disables any check contraints that reference the target table.
    This is useful for when referenced data is going to be temporarily modified
    in a way that will invalidate any check constraints.
    02-Jun-2015 S.Wilber
    @target_schema_name sysname
   ,@target_table_name sysname
   ,@do_set_enabled bit

-- declare variables
Declare     @constraint_name sysname
           ,@referencing_schema_name sysname
           ,@referencing_table_name sysname
           ,@sql nvarchar(max)

Declare @constraints table
    ConstraintName sysname
   ,ReferencingSchemaName sysname
   ,ReferencingTableName sysname

Insert      @constraints (ConstraintName, ReferencingSchemaName, ReferencingTableName)
Select      fk.name As ConstraintName
           ,sch.name As ReferencingSchemaName
           ,t.name As ReferencingTableName
From        sys.foreign_keys fk
Inner Join  sys.tables t On (t.object_id = fk.parent_object_id)
Inner Join  sys.schemas sch On (sch.schema_id = t.schema_id)
Where       fk.referenced_object_id = Object_ID(QuoteName(@target_schema_name) + '.' + QuoteName(@target_table_name))

Declare     constraint_cursor Cursor Fast_Forward For
Select      ConstraintName
From        @constraints

Open constraint_cursor
Fetch From constraint_cursor Into @constraint_name, @referencing_schema_name, @referencing_table_name

While @@Fetch_Status = 0
    If @do_set_enabled = 1
        Set @sql = 'Alter Table ' + QuoteName(@referencing_schema_name) + '.' + QuoteName(@referencing_table_name) + ' With Check Check Constraint ' + QuoteName(@constraint_name)
        Set @sql = 'Alter Table ' + QuoteName(@referencing_schema_name) + '.' + QuoteName(@referencing_table_name) + ' NoCheck Constraint ' + QuoteName(@constraint_name)

    Exec sp_executesql @sql

    -- get the next constraint
    Fetch From constraint_cursor Into @constraint_name, @referencing_schema_name, @referencing_table_name

Close constraint_cursor
Deallocate constraint_cursor
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

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

%d bloggers like this: