SA0075 : Avoid constraints created with system generated name

Introduction

Ensure that constraints in a SQL Server database have meaningful names to improve database management and readability.

Description

In SQL Server databases, constraints like PRIMARY KEY , FOREIGN KEY , UNIQUE , and CHECK ensure data integrity. However, when these constraints are automatically named by the system, their naming is often non-descriptive and difficult to understand. This can complicate database management and troubleshooting, making it harder for developers and administrators to identify the purpose and function of these constraints.

For example:

-- Example of a system-generated constraint name
CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    EmployeeName NVARCHAR(100) NOT NULL
);
-- Constraint will be automatically named as "PK__Employees__3214EC07DAE0CAD7"

When constraints have system-generated names like PK__Employees__3214EC07DAE0CAD7 , it becomes challenging to identify what this constraint refers to without additional investigation. This can lead to confusion during database analysis and maintenance.

Non-descriptive names impede understanding of database structures, especially in large schemas.
System-generated names may result in difficulties when scripting database changes or performing queries related to constraints.

How to fix

Assign meaningful names to constraints to enhance database management and readability.

Follow these steps to address the issue:

1.Identify the constraint with a system-generated name that needs to be renamed using SELECT statements on sys.objects or sys.constraints . For example:

2.Drop the existing constraint using the ALTER TABLE statement. Use ALTER TABLE TableName DROP CONSTRAINT ConstraintName; .

3.Add the constraint again with a descriptive name using the ALTER TABLE statement. For instance, to rename a PRIMARY KEY constraint:

For example:

    -- Step 1: Example to find the existing constraint
    SELECT name
    FROM sys.objects
    WHERE object_id = OBJECT_ID(N'sys.default_constraints')
    AND parent_object_id = OBJECT_ID('Employees');

    -- Step 2: Drop system-generated constraint
    ALTER TABLE Employees DROP CONSTRAINT PK__Employees__3214EC07DAE0CAD7;

    -- Step 3: Add constraint with a meaningful name
    ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

Scope

The rule has a ContextOnly scope and is applied only on current server and database schema.

Parameters

Rule has no parameters.

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

8 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules