Skip to content

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.

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:

CREATE TABLE Employees (
ID INT,
Age INT CHECK (Age >= 18)
);

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.

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:

-- Example to find existing check constraints
SELECT c.name, t.name AS TableName
FROM sys.check_constraints AS c
JOIN sys.tables AS t ON c.parent_object_id = t.object_id;
-- Example to rename a constraint
EXEC sp_rename 'Check_Constraint_OldName', 'CK_Employees_Age', 'OBJECT';

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

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_]+

The rule does not need Analysis Context or SQL Connection.

8 minutes per issue.

Naming Rules, Code Smells

There is no additional info for this rule.

CREATE TABLE tt.Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CONSTRAINT CHK_Age CHECK (Age>=18)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int NOT NULL CONSTRAINT CHK_Age CHECK (Age>=18)
);
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
ALTER TABLE Persons
ADD Age int CONSTRAINT CHK_Age CHECK (Age>=18);
ALTER TABLE Persons
ADD Age int NOT NULL CONSTRAINT CHK_Age CHECK (Age>=18);
ALTER TABLE Persons
ADD CONSTRAINT CHK_Age CHECK (Age>=18 AND City='Sandnes');
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age>=18)
);
ALTER TABLE Persons
ADD CHECK (Age>=18);
  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 [CKPersons[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

Analysis Rules