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

Avoid undesired modification or deletion of more rows than intended due to incomplete filtering in UPDATE or DELETE statements.

Description

One common issue in T-SQL is accidentally altering or deleting more rows than intended. This typically occurs when UPDATE or DELETE statements are executed without using all columns of a table’s PRIMARY KEY or UNIQUE KEY in the filtering conditions.

For example:

SQL
Copy
1-- Example of problematic query that might affect too many rows
2DELETE FROM Employees WHERE EmployeeID = 1;

In this example, if EmployeeID is not the sole component of the primary or unique key, this query might not restrict the deletion as precisely as intended, leading to data integrity issues.

  • Unintentionally affects multiple rows, causing data loss or corruption.

  • Violates database constraints, potentially leading to orphan records or referential integrity problems.

How to fix

Ensure UPDATE and DELETE statements only affect the intended rows by properly using primary or unique key constraints in the filtering conditions.

Follow these steps to address the issue:

  1. Identify the primary or unique key columns within the target table. Ensure that your filtering conditions appropriately include these key columns to avoid affecting unintended rows.

  2. Revise your UPDATE and DELETE statements to include all necessary key columns in the WHERE clause. This ensures that operations are restricted to specific rows.

  3. Test the modified statements in an isolated environment to confirm they behave as expected, affecting only the desired rows.

  4. If you confirm the existing filters are accurate, but the rule still flags a violation, you may suppress the rule violation confidently, having verified proper usage.

For example:

SQL
Copy
1-- Corrected query with primary key consideration
2DELETE FROM Employees WHERE EmployeeID = 1 AND DepartmentID = 10;

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
Copy
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 Person.Address SET AddressLine1 = 'Address Line' WHERE AddressLine1 = 'Address 1' AND AddressLine2 = 'Address 2'
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 ([City],[StateProvinceID],[PostalCode]) are not referenced in the statement’s filter. 7 0
See Also

Other Resources

Analysis Rules