1

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!

4

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
| 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
0

No, thats not supported!

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

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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