Does SQL Server allow constraint violations in a transaction as long as it's not committed yet?
No, sorry. SQL Server does not allow deferred contraints in a transaction. It was present in SQL Server 6.5, but removed in SQL Server 2000:
SET DISABLE_DEF_CNST_CHK ON
Each individual statement must be consistent etc, regardless of whether it is in a transaction
Some RDBMS do allow this (e.g. Oracle, Postgres, Interbase)
Connect
There is a Microsoft Connect request, created in 2006, asking for this feature:
Option to defer foreign key constraint checking until transaction commit
There are various "chicken and egg" scenarios where it would be desirable to defer the checking of referential integrity constraints until commit time on a transaction.
Allow deferring of referential integrity constraint checking until commit time on a transaction (as an option). Suggest providing an option on BEGIN TRANSACTION that specifies this.
The last response from Microsoft came a decade ago:
Posted by Sameer [MSFT] on 10/13/2006 at 1:35 PM
Hello Greg,
Thanks for the feedback. We are aware of this and looking into it for a future release.
Sameer Verkhedkar
SQL Engine
[MSFT]
Which is Microsoft speak for "go away".
SQL-92 defines it
The feature was defined in July 1992 with SQL-92. An example syntax would be:
BEGIN TRANSACTION
SET CONSTRAINTS ALL DEFERRED --applies only to the current transaction
INSERT Customers ...
INSERT Orders ...
UPDATE Customers ... --add the thing we were missing
COMMIT TRANSACTION
You can disable your constraints while running your transaction, and then reenabling them when you are done.
ALTER TABLE mytable NOCHECK CONSTRAINT myconstraint
--... RUN TRANSACTION
ALTER TABLE mytable WITH CHECK CHECK CONTRAINT ALL
Warning: This will affect all connections.