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

The topic describes the SA0068B analysis rule.

Message

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

Description

The rule checks the naming of all check constraints used in CREATE TABLE and ALTER TABLE statements in the current sql script.

Separate name patterns are available for column level check constraints and table level check constraints.

The following placeholders will be replaced in the pattern during the key testing:

{table_name} – Name of the constraint’s parent table.

{column_name} – Target column name.

Regular expression patterns can be used, but the pattern must be prefixed with ‘regexp:’ string in order to be used as a matching regular expression.

How to fix

Review the constraint name and rename it according to the naming convention.

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