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

constraintsql servertransaction

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!

Best Answer

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

Related Solutions

Mysql – Adding foreign keys constraint on databases used

OK - following my comment, here is what I would do.

Before shutting down the server or individual database.

Create backup table scripts for each table you need to enforce foreign keys on and their parent(s). Call them my_table_bak (or similar).
Run these CREATE TABLE my_table_bak(1..n) scripts on the database (don't definitively delete anything).
(removed during edit)

Prepare an SQL script file (salvage.sql? :-))

Now, the question is are you removing orphan records (i.e. child records that have no corresponding record in the parent table)? Or are you deleting barren parents (i.e. parents with no child records)? Below will do both.

INSERT INTO parent_bak 
SELECT * FROM parent 
WHERE parent.primary_key NOT IN 
(
    SELECT child.foreign_key 
    FROM child
); -- backup barren parents

INSERT INTO child_bak 
SELECT * FROM child 
WHERE child.foreign_key 
NOT IN 
(
    SELECT parent.primary_key 
    FROM parent
); -- backup orphan records

DELETE FROM parent 
WHERE parent.primary_key 
NOT IN 
(
    SELECT child.foreign_key 
    FROM child
); -- remove invalid records from your original tables.

DELETE FROM child 
WHERE child.foreign_key NOT IN 
(
    SELECT parent.primary_key 
    FROM parent
); -- remove invalid records from your original tables.

Add your FOREIGN KEY constraints.

Stop all clients connecting to the server or database (so updates can't occur while running the copy into _bak tables (invalid data) and the deletes.
Run:
```
mysql -h host -u username -ppassword yourdb < salvage.sql
```
...for each of your servers. For your 20 databases, you could write a script for all 20 and run that.

The time this will take will depend on the size of your databases and the number of tables. Warning - there may be typos in this post - I didn't run the scripts on a server, but you should get the idea.

Test on at least one database before running on your live server.

[EDIT] Forgot - add your FOREIGN KEY constraints at the end of salvage.sql also.

Oracle – Understanding Deferrable Foreign Key Constraints

Deferrable constraints are usually wrong. It's a proof that your ORM does something wrong. Usual problems are

commit might fail. This is usally not expexted by various tools and also by developers
commit might last very long. This might be problem with distributed(XA) transactions.
also troubleshooting might be problematic, especially when using Java(JDBC). When commit fails you can hardly say which particular constraint failed the transaction

Best Answer

Related Solutions

Mysql – Adding foreign keys constraint on databases used

Oracle – Understanding Deferrable Foreign Key Constraints

Related Question