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

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

Description

Primary keys must be uniquely defined within a table to maintain data integrity and ensure efficient database operations. However, misnaming or using inconsistent patterns for primary key names can cause confusion and make future database maintenance more challenging. Establishing a naming convention for primary keys is crucial for clarity and consistency, especially in dynamic teams or complex projects.

For example:

SQL
Copy
1-- Example of a primary key with a non-descriptive name
2CREATE TABLE Customer (
3    ID INT PRIMARY KEY
4);

In this example, the primary key is named ID, which may not indicate its role or context within the database, leading to potential misunderstandings or errors during database administration or application development.

  • Falling short of a naming convention can result in difficulties when attempting to understand the purpose or structure of the database schema.

  • Inconsistent naming can complicate automated scripts or tools that rely on predictable naming patterns to function correctly.

How to fix

Renaming constraints to follow a consistent naming convention improves clarity and maintenance of your database.

Follow these steps to address the issue:

  1. Identify the primary key constraint with a non-descriptive or inconsistent name using INFORMATION_SCHEMA.TABLE_CONSTRAINTS.

  2. Determine a clear and descriptive naming convention for primary key constraints, such as PK_[TableName]_[ColumnName].

  3. Rename the primary key constraint using the sp_rename stored procedure.

For example:

SQL
Copy
1-- Find the current name of the primary key constraint
2SELECT CONSTRAINT_NAME 
3FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS 
4WHERE TABLE_NAME = 'Customer' 
5AND CONSTRAINT_TYPE = 'PRIMARY KEY';
6
7-- Rename the primary key to follow the naming convention
8EXEC sp_rename 'Customer.ID', 'PK_Customer_ID', 'OBJECT';

Scope

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

Parameters
Name Description Default 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.

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 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
  Message Line Column
1 SA0070B : The primary key name PK_Person does not match the naming convention. The expected primary key name is [PK_Persons_ID]. 6 15
2 SA0070B : The primary key name PK_Person does not match the naming convention. The expected primary key name is [PK_Persons_ID_LastName]. 10 15
3 SA0070B : The primary key name PK_Persons does not match the naming convention. The expected primary key name is [PK_Persons_ID]. 19 31
See Also

Other Resources

Analysis Rules