SQL Server 2008 R2 - How to check Foreign Key constraints in a transaction only when committed?

Question

We are synchronizing tables between two databases (yes, it needs to be done by our software and not by replication etc.)

Both databases are identical with many foreign-key constraints on tables.

Within a transaction we insert rows to various tables. The whole transaction will leave the tables in a state where all constraints are fulfilled.

But it may very well be, that in an intermediate state of the transaction a row gets inserted with a FK of another table that has not been inserted yet.

Is there a way to let SQL Server accept this and only check for violations once the whole transaction is committed?

Thanks for your help!

Answer 1

No, server checks Foreign keys for every statement inside transaction

The way is to disable all the FKeys in target DB just after the beginning of transaction and enable them back just before commit.

Useful script:

DECLARE @sql NVARCHAR(MAX) = ''

SELECT @sql = @sql + N'ALTER TABLE [' + OBJECT_SCHEMA_NAME(fk.parent_object_id)+N'].['+OBJECT_NAME(fk.parent_object_id)+N'] NOCHECK CONSTRAINT ['+fk.NAME+N'];'+NCHAR(13)+NCHAR(10)
FROM sys.foreign_keys fk

PRINT @sql

Answer 2

No, thats not supported!

You should try to arrange your statements in the correct order which avoids FK violation.