Turn off constraints temporarily (MS SQL)

You can actually disable all database constraints in a single SQL command and the re-enable them calling another single command. See:

• Can foreign key constraints be temporarily disabled using TSQL?

I am currently working with SQL Server 2005 but I am almost sure that this approach worked with SQL 2000 as well

And, if you want to verify that you HAVEN'T broken your relationships and introduced orphans, once you have re-armed your checks, i.e.

ALTER TABLE foo CHECK CONSTRAINT ALL

or

ALTER TABLE foo CHECK CONSTRAINT FK_something

then you can run back in and do an update against any checked columns like so:

UPDATE myUpdatedTable SET someCol = someCol, fkCol = fkCol, etc = etc

And any errors at that point will be due to failure to meet constraints.

You can disable FK and CHECK constraints only in SQL 2005+. See ALTER TABLE

ALTER TABLE foo NOCHECK CONSTRAINT ALL

or

ALTER TABLE foo NOCHECK CONSTRAINT CK_foo_column

Primary keys and unique constraints can not be disabled, but this should be OK if I've understood you correctly.

-- Disable the constraints on a table called tableName:
ALTER TABLE tableName NOCHECK CONSTRAINT ALL

-- Re-enable the constraints on a table called tableName:
ALTER TABLE tableName WITH CHECK CHECK CONSTRAINT ALL
---------------------------------------------------------

-- Disable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'

-- Re-enable constraints for all tables in the database:
EXEC sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL'
---------------------------------------------------------