SA0075B : Avoid adding constraints with default system generated name

Introduction

Unspecified constraint names in CREATE TABLE and ALTER TABLE statements lead to potential issues with maintainability and debugging in SQL Server databases.

Description

When constraints, such as primary keys, foreign keys, or checks, are created without explicit names in T-SQL CREATE TABLE or ALTER TABLE statements, SQL Server assigns system-generated names. These names are often non-descriptive and can make it challenging to identify and manage constraints, especially in complex databases where changes are frequent. Without specific names, debugging and maintaining database integrity become more difficult.

For example:

-- Unspecified constraint name
CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    CONSTRAINT UNIQUE (EmployeeID)
);

In this example, SQL Server generates a name for the constraint automatically. This lack of specificity may lead to confusion when reviewing or modifying the database later.

System-generated constraint names are non-intuitive, complicating troubleshooting processes.
Explicit constraint names provide clarity and facilitate database management and scripts understanding.

How to fix

Provide explicit names for constraints in CREATE TABLE and ALTER TABLE statements to improve maintainability and debugging.

Follow these steps to address the issue:

1.Identify the constraints in your CREATE TABLE or ALTER TABLE statements that lack explicit names, such as primary keys, foreign keys, or check constraints.

2.Modify these statements to include a descriptive name using the CONSTRAINT keyword followed by a chosen name and the constraint definition.

3.Use a consistent naming convention for constraints to further aid in database management and ensure that names are unique across the database.

For example:

-- Corrected table creation with named constraint
CREATE TABLE Employees (
    EmployeeID INT NOT NULL,
    CONSTRAINT UQ_Employee_EmployeeID UNIQUE (EmployeeID)
);

Scope

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

Parameters

Name	Description	Default Value
IgnoreTemporaryTables	Ignore temporary tables.	yes

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 REFERENCES Persons(LastName),
    FirstName varchar(255),
    Age int,
    UniqueId int UNIQUE CLUSTERED,
    CONSTRAINT PK_Person PRIMARY KEY (ID),
    FOREIGN KEY (FirstName,LastName) REFERENCES Persons(FirstName,LastName)
);

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

Analysis Results

	Message	Line	Column
1	SA0075B : The constraint will be added with default system generated name.	3	35
2	SA0075B : The constraint will be added with default system generated name.	6	17
3	SA0075B : The constraint will be added with default system generated name.	8	4
4	SA0075B : The constraint will be added with default system generated name.	13	0

Analysis Rules