Click or drag to resize

SA0070B : Check all Primary Key Constraints in the current sql script for following specified naming convention.(Batch)

The topic describes the SA0070B analysis rule.

Message

Check all Primary Key Constraints in the current sql script for following specified naming convention.(Batch)

Description

The rule checks the naming of all primary keys used in CREATE TABLE and ALTER TABLE statements.

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_list} - List of the key columns, ordered by key ordinal and separated by underscore('_').

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.

Scope

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

Parameters
NameDescriptionDefault Value
NamePattern

Primary key name pattern.

PK_{table_name}_{column_list}

ColumnsListSeprator

Separator which to be used for separating the columns in the {column_list} placeholder.

_

Remarks

The rule does not need Analysis Context or SQL Connection.

Categories

Naming Rules, New Rules

Additional Information

There is no additional info for this rule.

Example Test Script
 1CREATE TABLE Persons (
 2    ID int NOT NULL,
 3    LastName varchar(255) NOT NULL,
 4    FirstName varchar(255),
 5    Age int,
 6    CONSTRAINT PK_Person PRIMARY KEY (ID)
 7);
 8
 9ALTER TABLE Persons
10ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName),
11FOREIGN KEY (FirstName,LastName) REFERENCES Persons(FirstName,LastName);
12
13CREATE TABLE Test.Greeting (
14    greetingId INT IDENTITY (1,1) PRIMARY KEY,
15    Message nvarchar(255) NOT NULL,
16);
17
18CREATE TABLE Persons (
19    ID int NOT NULL CONSTRAINT PK_Persons PRIMARY KEY,
20    LastName varchar(255) NOT NULL,
21    FirstName varchar(255),
22    Age int
23);
24
25CREATE TABLE Persons (
26    ID int PRIMARY KEY,
27    LastName varchar(255) NOT NULL,
28    FirstName varchar(255),
29    Age int
30);
31
32CREATE TABLE Persons (
33    ID int NOT NULL,
34    LastName varchar(255) NOT NULL,
35    FirstName varchar(255),
36    Age int,
37    PRIMARY KEY (ID,LastName)
38);
39
40ALTER TABLE Persons
41ADD PRIMARY KEY (ID);

Analysis Results

 MessageLineColumn
1SA0070B : The primary key name PK_Person does not match the naming convention. The expected primary key name is [PK_Persons_ID].615
2SA0070B : The primary key name PK_Person does not match the naming convention. The expected primary key name is [PK_Persons_ID_LastName].1015
3SA0070B : The primary key name PK_Persons does not match the naming convention. The expected primary key name is [PK_Persons_ID].1931
See Also

Other Resources