Skip to content

SA0095 : The updated column is a primary key column

Modifying key columns can lead to performance issues and data integrity problems.

In SQL Server, altering key columns in an UPDATE statement can cause considerable issues. Key columns include the primary key, unique keys, and the clustered index, which are crucial for efficiently maintaining data integrity and retrieval operations. Modifying these keys can result in significant performance degradation and complicate data integrity constraints.

For example:

-- Example of a problematic update statement
UPDATE Customers
SET CustomerID = CustomerID + 1
WHERE CustomerID = 123;

In this example, changing the CustomerID , which is likely a primary key, can lead to unexpected behavior, such as row duplication, index fragmentation, and even breaking foreign key references.

  • Performance degradation due to increased index maintenance and potential fragmentation.

  • Potential loss of data integrity, leading to broken foreign key references and duplicate primary keys.

`

This guide provides a step-by-step approach to addressing issues related to the modification of key columns in an SQL Server database.

Follow these steps to address the issue:

1.Do not update primary key columns directly. Instead, consider alternative approaches such as marking records as inactive or using surrogate keys for updates. Review your schema design to determine if changes can be made to accommodate this.

2.If an update is unavoidable, ensure that all foreign key references are updated accordingly. This may be done programmatically to maintain referential integrity using ON UPDATE CASCADE constraints.

3.Ensure that all updates account for indexes, as extensive operations on key columns will lead to index maintenance overhead. Consider indexing strategies that minimize performance impact.

4.If Change Tracking is enabled, ensure planned updates are tested and verified to maintain synchronization integrity. This may involve additional application logic or synchronization methods.

5.Verify that external systems interfacing with your database are reviewed for update handling to prevent broken references.

For example:

-- Example of an alternative approach using a surrogate key or marking inactive
ALTER TABLE Customers ADD IsActive BIT DEFAULT 1;
UPDATE Customers SET IsActive = 0 WHERE CustomerID = 123;
-- Or, consider a surrogate key approach where necessary
ALTER TABLE Orders ADD NewKey UNIQUEIDENTIFIER DEFAULT NEWID();

The rule has a Batch scope and is applied only on the SQL script.

Name Description Default Value
RequireUpdatedColumnTableChangeTrackingEnabled Specifies if the rule should consider only updated tables which have Change Tracking enabled. no
RequireUpdatedColumnInClusteredIndex Specifies if the rule should consider only updated columns which are part of table’s clustered index. no
RequireUpdatedColumnInPrinaryKey Specifies if the rule should consider only updated columns which are part of table’s primary key. yes
RequireUpdatedColumnInUniqueIndex Specifies if the rule should consider only updated columns which are part of unique index. no
RequireUpdatedColumnForeignKeykReferenced Specifies if the rule should consider only updated columns which are referenced by a foreign key. no
RequireUpdatedColumnInNonClusteredIndex Specifies if the rule should consider only updated columns which are part of non-clustered index. no

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

1 hour per issue.

Design Rules, Bugs

About Change Tracking

-- HumanResources.Department.DepartmentId is primary key column and is referenced by foreign keys
UPDATE dep
SET DepartmentID = 12
FROM HumanResources.Department dep
WHERE
dep.DepartmentId = 8
-- Person.Address.StateProvinceID is not in primary or unique index
UPDATE Person.Address
SET StateProvinceID = 77
FROM Person.Address a
WHERE
a.AddressLine1 = '34 Waterloo Road' AND
a.City = 'Melbourne'
-- Person.AddressType.AddressTypeId is primary key column and is referenced by foreign keys
UPDATE Person.AddressType SET AddressTypeID = 0 WHERE Name = 'Primary'
-- Person.AddressType.Name is in unique index and is not referenced by foreign keys
UPDATE Person.AddressType SET Name = 'Primary1',ModifiedDate = GetDate() WHERE AddressTypeId = 0
-- Production.ProductModel.Name is in unique index and is not referenced by foreign keys
UPDATE Production.ProductModel SET Name = 'Name1' WHERE Name = 'Name0'
  Message Line Column
1 SA0095 : The table HumanResources.Department has its primary key column DepartmentID updated. 3 8
2 SA0095 : The table Person.AddressType has its primary key column AddressTypeID updated. 17 30

Analysis Rules