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

Introduction

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:

-- Example of a primary key with a non-descriptive name
CREATE TABLE Customer (
    ID INT PRIMARY KEY
);

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:

-- Find the current name of the primary key constraint
SELECT CONSTRAINT_NAME
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE TABLE_NAME = 'Customer'
AND CONSTRAINT_TYPE = 'PRIMARY KEY';

-- Rename the primary key to follow the naming convention
EXEC 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.

Additional Information

There is no additional info for this rule.

Example Test SQL

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    CONSTRAINT PK_Person PRIMARY KEY (ID)
);

ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName),
FOREIGN KEY (FirstName,LastName) REFERENCES Persons(FirstName,LastName);

CREATE TABLE Test.Greeting (
    greetingId INT IDENTITY (1,1) PRIMARY KEY,
    Message nvarchar(255) NOT NULL,
);

CREATE TABLE Persons (
    ID int NOT NULL CONSTRAINT PK_Persons PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

CREATE TABLE Persons (
    ID int PRIMARY KEY,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int
);

CREATE TABLE Persons (
    ID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID,LastName)
);

ALTER TABLE Persons
ADD 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

Analysis Rules