SA0068B : Check all Check Constraints in the current sql script for following specified naming convention

Inconsistent or unclear check constraint naming can lead to confusion and maintenance challenges.

Description

Constraints are rules enforced on data columns, allowing SQL Server to maintain the accuracy and reliability of data. Specifically, check constraints validate data based on logical conditions. However, issues arise when these constraints are not named consistently or meaningfully.

For example:

SQL
Copy
1CREATE TABLE Employees (
2    ID INT,
3    Age INT CHECK (Age >= 18)
4);

In this example, the check constraint does not have a specific name. This lack of naming can make it harder to identify and manage constraints during database updates or debugging sessions.

  • Unnamed constraints lead to ambiguous error messages when data violations occur, complicating troubleshooting processes.

  • Lack of standard naming makes database maintenance challenging, particularly when constraints need to be modified or documented.

How to fix

This section provides a strategy for renaming constraints to adhere to a consistent naming convention, ensuring easier maintenance and clearer identification.

Follow these steps to address the issue:

  1. Identify the unnamed or improperly named check constraint in your table. You can query the system views in SQL Server to list existing constraints. Use sys.check_constraints and sys.tables to find constraints associated with tables.

  2. Determine a meaningful and consistent naming convention for the constraint. A common practice is to include the table name and column name, such as CK_TableName_ColumnName.

  3. Rename the constraint using the sp_rename system stored procedure. Specify the old name of the constraint and the new, meaningful name according to your naming convention.

For example:

SQL
Copy
1-- Example to find existing check constraints
2SELECT c.name, t.name AS TableName 
3FROM sys.check_constraints AS c
4JOIN sys.tables AS t ON c.parent_object_id = t.object_id;
5
6-- Example to rename a constraint
7EXEC sp_rename 'Check_Constraint_OldName', 'CK_Employees_Age', 'OBJECT';

Scope

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

Parameters
Name Description Default Value
ColumnConstraintNamePattern

Column level check constraint name pattern.

chk{table_name}{column_name}
TableConstraintNamePattern

Table level check constraint name pattern.

regexp:CK_{table_name}_[A-Za-z_]+
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE tt.Persons (
 2    ID int NOT NULL,
 3    LastName varchar(255) NOT NULL,
 4    FirstName varchar(255),
 5    Age int CONSTRAINT CHK_Age CHECK (Age>=18)
 6); 
 7
 8CREATE TABLE Persons (
 9    ID int NOT NULL,
10    LastName varchar(255) NOT NULL,
11    FirstName varchar(255),
12    Age int NOT NULL CONSTRAINT CHK_Age CHECK (Age>=18)
13); 
14
15CREATE TABLE Persons (
16    ID int NOT NULL,
17    LastName varchar(255) NOT NULL,
18    FirstName varchar(255),
19    Age int,
20    City varchar(255),
21    CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
22); 
23
24ALTER TABLE Persons
25ADD Age int CONSTRAINT CHK_Age CHECK (Age>=18);
26
27ALTER TABLE Persons
28ADD Age int NOT NULL CONSTRAINT CHK_Age CHECK (Age>=18);
29
30ALTER TABLE Persons
31ADD CONSTRAINT CHK_Age CHECK (Age>=18 AND City='Sandnes'); 
32
33CREATE TABLE Persons (
34    ID int NOT NULL,
35    LastName varchar(255) NOT NULL,
36    FirstName varchar(255),
37    Age int CHECK (Age>=18)
38); 
39
40ALTER TABLE Persons
41ADD CHECK (Age>=18);
Analysis Results
  Message Line Column
1 SA0068B : The check constraint Persons.[CHK_Age] does not match the naming convention. The expected name is [chkPersonsAge]. 5 23
2 SA0068B : The check constraint Persons.[CHK_Age] does not match the naming convention. The expected name is [chkPersonsAge]. 12 32
3 SA0068B : The check constraint Persons.[CHK_Person] does not match the naming convention. The expected name is [CK_Persons_[A-Za-z_]+]. 21 15
4 SA0068B : The check constraint Persons.[CHK_Age] does not match the naming convention. The expected name is [chkPersonsAge]. 25 23
5 SA0068B : The check constraint Persons.[CHK_Age] does not match the naming convention. The expected name is [chkPersonsAge]. 28 32
See Also

Other Resources

Analysis Rules