Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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!

share|improve this question
    
Also see stackoverflow.com/q/5974731/27535 –  gbn Dec 20 '11 at 13:16

2 Answers 2

up vote 3 down vote accepted

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
share|improve this answer
5  
Or better yet, do your changes in the correct order so you won't have any FK violations. I'm not a fan of turning off FKs. –  HLGEM Dec 20 '11 at 21:31
    
@HLGEM: You completely right! But the issue states crystal clear question 8-) –  Oleg Dok Dec 21 '11 at 3:37

No, thats not supported!

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.