Why you should not update Primary Key values

Recently I had the chance to take a look at a problematic query in an application. I caught the query in profiler and it’s the following:

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
-- Modify the updatable columns
UPDATE [dbo].[StationaryMaster] 
SET 
[Id] = @Id, --comment this
    [HistoryId] = @HistoryId,
    [ServiceId] = @ServiceId,
    [ServiceName] = @ServiceName,
    [Status] = @Status,
    [OrderId] = @OrderId,
    [DoctorId] = @DoctorId,
    [TransferedFromDoctorId] = @TransferedFromDoctorId,
    [StationaryNumber] = @StationaryNumber,
    [StationaryYear] = @StationaryYear,
    [Protokol] = @Protokol,
    [Date] = @Date,
    [CreatedBy] = @CreatedBy,
    [ModifiedBy] = @ModifiedBy,
    [CreatedDate] = @CreatedDate,
    [ModifiedDate] = @ModifiedDate
WHERE 
      [Id] = @OriginalId

The query was throwing the following error:

The query processor ran out of stack space during query optimization. Please simplify the query.

The error message is reported on connect, and the same is closed by design reasons. By database design, it’s a limitation for the number of foreign keys to 253.

Analysis of the code and values of @Id and @OriginalId variables showed that they have the same value. Additionally the PK occurs as a Foreign Key (FK) in 321 tables across the database.

I asked the developer if there is really a reason to update the primary key [Id]. After confirming this, we removed the [Id] = @Id in the SET part of the query and the problem disappeared.

Conclusion

Primary keys should not be updated. If it’s referenced in many tables, then its update withdraws updates for potentially many tables. Update in many tables could introduce significant fragmentation in the database. The update could happen to introduce data inconsistency so that the query will fail for other reasons. Even though PKs can be updated, they are not recommended to. Many other issues can occur in replicated databases which is another reason for not updating (possibly changing) primary key values.