Enable/Disable FK Constraints for reloading data

Scenario: Several tables in a database become corrupt (i.e. updates have happened on the tables which cant be backed out). You have a backup copy of the table and want to overwrite the existing tables with the backup copy.

If its a normalised database to some form, then there may be some foreign key constraints in the tables you want to overwrite. There are a couple of easy steps below which can be used to disable and enable constraints.

Script to Disable\Enable FK Constraints

SELECT 
'ALTER TABLE ' + OBJECT_NAME(FKEYID) + ' NOCHECK CONSTRAINT ' 
+ OBJECT_NAME(CONSTID) as 'DISABLE',
'ALTER TABLE ' + OBJECT_NAME(FKEYID) + ' CHECK CONSTRAINT ' 
+ OBJECT_NAME(CONSTID) as 'ENABLE',
(CASE
WHEN OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0 THEN 'ENABLED'
ELSE 'DISABLED'
END) AS STATUS,
OBJECT_NAME(CONSTID) AS CONSTRAINT_NAME,
OBJECT_NAME(FKEYID) AS TABLE_NAME,
COL_NAME(FKEYID, FKEY) AS COLUMN_NAME,
OBJECT_NAME(RKEYID) AS REFERENCED_TABLE_NAME,
COL_NAME(RKEYID, RKEY) AS REFERENCED_COLUMN_NAME
FROM SYSFOREIGNKEYS
WHERE OBJECTPROPERTY(CONSTID, 'CNSTISDISABLED') = 0
--Uncomment if you want to narrow down to constraints for a specific table
--AND (OBJECT_NAME(FKEYID) = 'SU_HELP_CENTRE' 
--    OR OBJECT_NAME(RKEYID) = 'SU_HELP_CENTRE')
ORDER BY TABLE_NAME, CONSTRAINT_NAME,REFERENCED_TABLE_NAME, KEYNO

The first column gives the disable dynamic SQL for all enabled constraints in a database.

The Second column gives the enable dynamic SQL so that you can revert the change back and only enable the constraints that were originally enabled.

Ensure you copy out both dynamic SQL scripts before continuing.

1. Run the Disable constraints script

2. Delete data from the affected tables that you want to overwrite

3. Import the backup data into the destination tables

4. Run the Enable constraints script

Why disable constraints at all?

To prevent getting integrity check errors against the constraints on the tables you are re-importing

Things to be aware of…

  • If re-importing data, make sure you re-import a consistent snapshot of the data, and you import all the tables that may be involved in a particular relationship – this is to prevent data integrity issues.
  • It might be said that its better to restore from a backup – but for the scenario we faces, we knew the relationship was contained to 3 tables, so we could easily manage integrity. Restoring the whole database wasn’t an option as only part of the data set was compromised while the rest was functioning ok.
Advertisements

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: