Transaction with multiple updates and unique index

Question

SQL Server has table, where one record is FK to the same table (parent record or 'null' if it's first record):

CREATE TABLE [dbo].[RegisteredDevice]
(
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [PreviousDeviceId] [int] NULL,
    [Position] [int] NULL,
    [DeviceName] [nvarchar](max) NOT NULL,
    [ModelNumber] [nvarchar](max) NOT NULL,

    CONSTRAINT [PK_RegisteredDevice] 
        PRIMARY KEY CLUSTERED ([Id] ASC)
                WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, 
                      IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, 
                      ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO

ALTER TABLE [dbo].[RegisteredDevice] WITH CHECK 
    ADD CONSTRAINT [FK_RegisteredDevice_RegisteredDevice_PreviousDeviceId] 
        FOREIGN KEY([PreviousDeviceId])
            REFERENCES [dbo].[RegisteredDevice] ([Id])
GO

ALTER TABLE [dbo].[RegisteredDevice] 
    CHECK CONSTRAINT [FK_RegisteredDevice_RegisteredDevice_PreviousDeviceId]
GO

I have the following SQL code:

BEGIN TRANSACTION
    UPDATE [RegisteredDevice] 
    SET [PreviousDeviceId] = 2
    WHERE [Id] = 5;

    UPDATE [RegisteredDevice] 
    SET [PreviousDeviceId] = 4
    WHERE [Id] = 2;

    UPDATE [RegisteredDevice] 
    SET [PreviousDeviceId] = 1
    WHERE [Id] = 3;

COMMIT TRANSACTION

For this data:

but this code is not executed inside transaction:

Msg 2601, Level 14, State 1, Line 4
Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (2).
The statement has been terminated.

Msg 2601, Level 14, State 1, Line 6
Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (4).
The statement has been terminated.

Msg 2601, Level 14, State 1, Line 8
Cannot insert duplicate key row in object 'dbo.RegisteredDevice' with unique index 'IX_RegisteredDevice_PreviousDeviceId'. The duplicate key value is (1).
The statement has been terminated.

If all these changes will be committed inside one transaction, there is no any duplicate key. Why transaction is not processed?

Answer 1

In SQL Server, constraint checks are not deferrable, and are always carried out per update statement, not per transaction. If you combine them into a single statement then it works as noted in the other answer.

You claim you cannot modify the query because it's generated by Entity Framework. But assuming you are using EF Core 7.0+, you can do the following bulk update query:

await Db.RegisteredDevice
    .Where(rd => rd.Id == 2 || rd.Id == 3 || rd.Id == 5)
    .ExecuteUpdateAsync(setter => setter
        .SetProperty(
            rd => rd.PreviousDeviceId,
            rd => rd.Id == 2 ? 4 :
                  rd.Id == 3 ? 1 :
                  2
         )
    );

Which will generate a batch update statement that does the same thing as the other answer.