SQL Server: How to ignore referential integrity until COMMIT?

Question

i have a process to move rows from one database to another. Because of some circular foreign key reference chains i cannot remove the rows from the old database, nor can i insert them into the new database.

Since the entire operation happens in a transaction¹, i want SQL Server to ignore referential integrity failures until i call COMMIT TRANSACTION.

For example²:

   Table: Turboencabulators         Table: Marselvanes
   =========================        =======================
PK TurboencabulatorID int    /-> PK MarselvaneID       int
^  MarselvanesID      int --/       HasGrammeter       bit
|                                   PantametricFan     varchar(50)
+-------------------------------    TurboencabulatorID int

If i try to insert the turboencabulator in the new table, it will fail without the marselvane already being there. Reversing the order has the same problem.

When trying to remove the old rows, i cannot delete one until the other is deleted.

i've tried doing an n-phase system, where all rows are inserted with any columns that are under foreign key constraint set to null. Then i update all the inserted rows, placing the proper missing values. Then, in order to delete the source rows, i null off all columns affected by a FK, then delete the actual rows.³

What i'd really prefer is to just do my T-SQL operations, and have SQL Server not tell me until i try to call commit.

Notes

¹distributed
²contrived hypothetical
³which i'm not doing anymore

Answer 1

you can use ...

ALTER TABLE whatever_table NOCHECK CONSTRAINT ALL 

to remove the constraint checking before you begin

and when done turn it back on with ...

ALTER TABLE whatever_table CHECK CONSTRAINT ALL 

that's what i'd do anyway.

-don

Answer 2

Imagine how would you implement that.

If foreign key resultion would be deffered until transaction commit, the commit would have to do all the lookups/checks/cascade operation that did not occur at insert/delete/update time. Think what a FK constraint really means: your insert execution plan gets 'annotated' with additional operation to validate and enforce the FK constraint. If you defer the constraint, the additional logic in the query plan would have to be unbound from the execution moment and pushed into some transaction context so that it is executed at commit time. All of the sudden commit turns from the short 'mark transaction commited in log' operation into an operation that does every thing that was skipped during the actual transaction. The worst part is that the constraint may fail, and think how would an application handle the failure? With a constraint enforced at the moment the insert is executed, the application can catch the error and take corrcetive actions: it kows exactly what failed. But if you defer this until commit, you try to commit and catch an exception, now you need to somehow figure out, from the cought exception, what failed. Think how complicated the life of application developers would be in that case.

A second reason why this wouldn't work is that you still didn't solve the problem. You have table A with FK constraint into B. You begin transaction, insert into B, then insert into A, then delete from A, then delete from B, then commit. All operations were satisfying the FK at the moment they occured, the database satisfies the FK at commit time. Yet if you defer the constraint checks, they will fail at commit time!!

So I'd say that the referential integrity works fine as it is, but it is designed fot a cascadin hierachy free of cycles. As many CS data structures and algorithms, it breaks when cycles are instroduced. The best solution would be to analyze the schema and see if the cycles are trully unavoidable. Short of that, inserting NULL and updating post insert is the best solution.

Unfortunately disabling the constraint and enabling back is a big no-no: the re-enable would have to check every row in the table to verify the constraint, and will last forever. Otherwise the constraint is marked as 'untrusted' in the database metadata and the optimizer will basically ignore it (will still be enforced, but you don't get plan optimization benefits from it).