SA0095 : The updated column is a primary key column |  |
The topic describes the SA0095 analysis rule.
MessageThe updated column is a primary key column
DescriptionThe 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.
ScopeThe rule has a Batch scope and is applied only on the SQL script.
Parameters| 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 |
RemarksThe rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.
CategoriesDesign Rules
Additional InformationExample Test Script1-- 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
| 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 |
See Also