SA0069B : Check all Default Constraints in the current script for following specified naming convention

The topic describes the SA0069B analysis rule.

Message

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

Description

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

The NamePattern variable can be used to select or configure the desired pattern which will be used to check the object name.

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

{table_name} – Name of the key’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
NamePattern

Deault constraint name pattern.

DF_{table_name}_{column_name}
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 Orders (
 2    ID int NOT NULL,
 3    OrderNumber int NOT NULL,
 4    OrderDate date CONSTRAINT DF_DefaultDate DEFAULT GETDATE()
 5);
 6
 7CREATE TABLE Orders (
 8    ID int NOT NULL,
 9    OrderNumber int NOT NULL CONSTRAINT DF_DefaultNumber DEFAULT 1,
10    OrderDate date CONSTRAINT DF_DefaultDate DEFAULT GETDATE()
11);
12
13ALTER TABLE Persons 
14ADD OrderDate date NOT NULL CONSTRAINT DF_DefaultDate DEFAULT GETDATE();
15
16ALTER TABLE Persons 
17ADD OrderDate date NOT NULL CONSTRAINT DF_DefaultDate DEFAULT GETDATE(),
18 OrderNumber INT CONSTRAINT DF_DefaultDate DEFAULT 1;
19
20ALTER TABLE Orders 
21ADD CONSTRAINT DF_DefaultDate DEFAULT GETDATE() FOR OrderDate,
22CONSTRAINT DF_DefaultDate DEFAULT 1 FOR OrderNumber;
23
24CREATE TABLE Persons (
25    ID int NOT NULL,
26    LastName varchar(255) NOT NULL,
27    FirstName varchar(255),
28    Age int,
29    City varchar(255) DEFAULT 'Sandnes'
30);
31
32CREATE TABLE Orders (
33    ID int NOT NULL,
34    OrderNumber int NOT NULL,
35    OrderDate date DEFAULT GETDATE()
36);
37
38ALTER TABLE Persons 
39ADD OrderDate date NOT NULL DEFAULT GETDATE();
Analysis Results
  Message Line Column
1 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Orders_OrderDate]. 4 30
2 SA0069B : The default constraint name DF_DefaultNumber does not match the naming convention. The expected default constraint name is [DF_Orders_OrderNumber]. 9 40
3 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Orders_OrderDate]. 10 30
4 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Persons_OrderDate]. 14 39
5 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Persons_OrderDate]. 17 39
6 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Persons_OrderNumber]. 18 28
7 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Orders_OrderDate]. 21 15
8 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Orders_OrderNumber]. 22 11
See Also

Other Resources

Analysis Rules