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.

If you must (such as a Process to cleanse data from an import file), then put the intermediate data into Temp tables or table variables or staging tables and then only do the actions to the real tables with the constraints after you have cleansed it and made the data correct.

SQL Server does not have deferred contraints option. But in some cases you can use Bulk Insert that supports ignoring constraints without problems of disabling. For more information you can see these links:

• Controlling Constraint Checking by Bulk Import Operations
• BULK INSERT and INSERT: Using OPENROWSET and BULK to Bulk Load Data

Then just before committing transaction, you will have to check the constraints on the whole table manually.

ALTERNATIVE you can use Merge commands. They resolve together, bypassing the constraint intermediary state. Here is an example. Before insert, John has pk id=1, Marta has pk id=2. Firstname and lastname are unique. The Merge command switch places and after the MERGE John has PK id=2 and Marta id=1. Switching Marta and Joe names are not possible with normal update commands.

CREATE TABLE dbo.People
(
    Id        int          NOT NULL IDENTITY,
    FirstName NVARCHAR(50) NOT NULL,
    LastName  NVARCHAR(50) NOT NULL,
    CONSTRAINT UQ_People UNIQUE (FirstName, LastName)
);

--SELECT * FROM People;
--TRUNCATE TABLE People;

INSERT INTO People (FirstName, LastName)
VALUES
    (N'John', N'Doe'),
    (N'Marta', N'Smith'),
    (N'Julius','Manfred');

MERGE dbo.People AS P
USING (
      SELECT 1 AS Id, 'Marta' AS FirstName, 'Smith' AS LastName
       UNION
      SELECT 2, 'John', 'Doe'
       UNION
      SELECT 8, 'Martin', 'Berg') AS Flip
ON P.Id = Flip.Id
WHEN MATCHED AND (P.FirstName <> Flip.FirstName OR P.LastName <> Flip.LastName) THEN
    UPDATE
       SET P.FirstName = Flip.FirstName,
           P.LastName  = Flip.LastName
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (FirstName,
     LastName)
    VALUES
        (Flip.FirstName,
         Flip.LastName)
WHEN NOT MATCHED BY SOURCE THEN DELETE
    OUTPUT INSERTED.*, DELETED.*, $action;

SELECT *
  FROM People;