SA0075B : Avoid adding constraints with default system generated name

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:

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.

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:

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

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

The rule does not need Analysis Context or SQL Connection.

Naming Rules, Bugs

There is no additional info for this rule.

Other Resources