SA0076 : Check UPDATE and DELETE statements for not filtering using all columns of the table’s PRIMARY KEY or UNIQUE KEY

 

The topic describes the SA0076 analysis rule.

Message

Check UPDATE and DELETE statements for not filtering using all columns of the table’s PRIMARY KEY or UNIQUE KEY

Description

 

The rule tries to prevent deletion or updating of more rows than desired.

It checks UPDATE and DELETE statements for not using all the columns of the target table’s PRIMARY KEY or UNIQUE KEY in the statement’s filtering clauses.

How to fix

 

Review the UPDATE and DELETE statements for not affecting more rows than desired. Suppress the rule violation if the statements filters are correct.

Scope

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

Parameters
Name Description Default Value
ConsiderPrimaryKeysOnly The parameter specifies whether the unique keys are also considered or not. no
RequireKeyWithClusteredIndex The parameter makes the rule to require the unique or primary key to have clustered index. no
SkipStatementsNotHavingWhereClause The parameter can be used to force the rule to ignore UPDATE and DELETE statements which do not have WHERE clause. This way you can avoid duplicating the results from rules SA0036 and SA0037. yes
ConsiderJoinedTableUniqueKey The parameter makes the rule to check if any of the joined table for having all unique columns referenced and ignore the statement if is such table source is matched. yes
CheckWhereClauseConditions The parameter makes the rule to check filtering conditions in the WHERE clause. yes
CheckJoinOnClauseConditions The parameter makes the rule to check filtering conditions in the JOIN’s ON clauses. Outer joined tables are always ignored. no
Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix
1 hour per issue.
Categories

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1UPDATE edh SET  ModifiedDate = getdate(  )
 2FROM  HumanResources.EmployeeDepartmentHistory AS edh
 3WHERE  ShiftID = 5 AND
 4       StartDate IN( '2010-05-22', '2010-05-22' ) AND
 5       DepartmentID LIKE '43 '
 6
 7UPDATE t  SET  ModifiedDate = getdate(  )
 8FROM  #TempTable t
 9
10UPDATE HumanResources.EmployeeDepartmentHistory  SET  ModifiedDate = getdate(  )
11FROM  HumanResources.EmployeeDepartmentHistory 
12
13UPDATE Person.Address SET AddressLine1 = 'Address Line' WHERE AddressLine1 = 'Address 1'
14
15UPDATE Person.Address SET AddressLine1 = 'Address Line' WHERE AddressLine1 = 'Address 1' AND AddressLine2 = 'Address 2'
16
17UPDATE Person.Address SET AddressLine1 = 'Address Line' 
18
19UPDATE Person.Address SET  AddressLine1 = 'Address Line'  WHERE AddressID = 5
20
21UPDATE HumanResources.Shift SET EndTime = dateadd(day,EndTime ,1) WHERE StartTime > getdate()
22
23DELETE FROM HumanResources.Shift WHERE StartTime > getdate()
24
25DELETE FROM HumanResources.NonExistingTable WHERE StartTime > getdate()
26
27UPDATE  HumanResources.EmployeeDepartmentHistory 
28SET  ModifiedDate = getdate(  )
29FROM  HumanResources.EmployeeDepartmentHistory ed
30LEFT OUTER JOIN HumanResources.Department AS d
31ON  d.DepartmentID =  ed.DepartmentID
32LEFT OUTER JOIN HumanResources.Employee AS e
33ON  e.BusinessEntityID =  ed.BusinessEntityID
34WHERE  ShiftID = 5 AND  
35       ed.StartDate IN( '2010-05-22', '2010-05-22' ) AND 
36       d.DepartmentID LIKE '43 '

Analysis Results
Message Line Column
1 SA0076 : The UPDATE statement may affect more than the expected rows in table [HumanResources].[EmployeeDepartmentHistory].The Primary Key [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] columns ([BusinessEntityID]) are not referenced in the statement’s filter. 1 0
2 SA0076 : The UPDATE statement may affect more than the expected rows in table [Person].[Address].The Unique Key [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] columns ([AddressLine2],[City],[StateProvinceID],[PostalCode]) are not referenced in the statement’s filter. 13 0
3 SA0076 : The UPDATE statement may affect more than the expected rows in table [Person].[Address].The Unique Key [IX_Address_AddressLine1_AddressLine2_City_StateProvinceID_PostalCode] columns ([City],[StateProvinceID],[PostalCode]) are not referenced in the statement’s filter. 15 0
4 SA0076 : The UPDATE statement may affect more than the expected rows in table [HumanResources].[Shift].The Unique Key [AK_Shift_StartTime_EndTime] columns ([EndTime]) are not referenced in the statement’s filter. 21 0
5 SA0076 : The DELETE statement may affect more than the expected rows in table [HumanResources].[Shift].The Unique Key [AK_Shift_StartTime_EndTime] columns ([EndTime]) are not referenced in the statement’s filter. 23 0
6 SA0076 : The UPDATE statement may affect more than the expected rows in table [HumanResources].[EmployeeDepartmentHistory].The Primary Key [PK_EmployeeDepartmentHistory_BusinessEntityID_StartDate_DepartmentID] columns ([BusinessEntityID],[DepartmentID]) are not referenced in the statement’s filter. 27 0
See Also

Other Resources