Click or drag to resize

SA0095 : The updated column is a primary key column

The topic describes the SA0095 analysis rule.

Message

The updated column is a primary key column

Description

The rule checks code for UPDATE statements which depending on the rule settings modify target table's primary key, unique key or clustered index.

Considerations when doing primary key update:

  • All the foreign keys that reference the updated key have to be also updated. If the foreign keys are indexed, it will cause their indexes to be also updated, which can be an expensive operation. Otherwise, if no index exists for foreign key columns, a table lock will be applied.

  • When the table has Change Tracking enabled, the values of the primary key column identify the rows that have been changed and this is the only information from the tracked table that is recorded with the change information. If the synchronization of the changed data is implemented based on the Change Tracking, it will fail because of the modified primary key column values.

  • If this key is referenced in any external system the reference will be broken upon update.

  • The primary keys are usually clustered. Updating the table's clustered index will cause also update of the existing non-clustered indexes.

Scope

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

Parameters
NameDescriptionDefault 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

Remarks

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

Categories

Design Rules

Additional Information
Example Test Script
 1-- HumanResources.Department.DepartmentId is primary key column and is referenced by foreign keys
 2UPDATE  dep
 3SET     DepartmentId = 12
 4FROM    HumanResources.Department dep
 5WHERE 
 6        dep.DepartmentId = 8
 7
 8-- Person.Address.StateProvinceID is not in primary or unique index
 9UPDATE  Person.Address
10SET     StateProvinceID = 77 
11FROM    Person.Address a
12WHERE 
13        a.AddressLine1 = '34 Waterloo Road' AND 
14        a.City = 'Melbourne'
15
16-- Person.AddressType.AddressTypeId is primary key column and is referenced by foreign keys
17UPDATE Person.AddressType SET AddressTypeId = 0 WHERE Name = 'Primary'
18
19
20-- Person.AddressType.Name is in unique index and is not referenced by foreign keys
21UPDATE Person.AddressType SET Name = 'Primary1',ModifiedDate = GetDate() WHERE AddressTypeId = 0
22
23-- Production.ProductModel.Name is in unique index and is not referenced by foreign keys
24UPDATE Production.ProductModel SET Name = 'Name1' WHERE Name = 'Name0'

Analysis Results

 MessageLineColumn
1SA0095 : The table HumanResources.Department has its primary key column DepartmentId updated.38
2SA0095 : The table Person.AddressType has its primary key column AddressTypeId updated.1730
See Also

Other Resources