SA0075B : Avoid adding constraints with default system generated name

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:

SQL
1-- Unspecified constraint name
2CREATE TABLE Employees (
3    EmployeeID INT NOT NULL,
4    CONSTRAINT UNIQUE (EmployeeID)
5);

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:

SQL
1-- Corrected table creation with named constraint
2CREATE TABLE Employees (
3    EmployeeID INT NOT NULL,
4    CONSTRAINT UQ_Employee_EmployeeID UNIQUE (EmployeeID)
5);

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.
Categories

Naming Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1CREATE TABLE Persons (
 2    ID int NOT NULL,
 3    LastName varchar(255) NOT NULL REFERENCES Persons(LastName),
 4    FirstName varchar(255),
 5    Age int,
 6    UniqueId int UNIQUE CLUSTERED,
 7    CONSTRAINT PK_Person PRIMARY KEY (ID),
 8    FOREIGN KEY (FirstName,LastName) REFERENCES Persons(FirstName,LastName)
 9);
10
11ALTER TABLE Persons ADD 
12CONSTRAINT PK_Person PRIMARY KEY (ID,LastName),
13FOREIGN 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
See Also

Other Resources