SA0075 : Avoid constraints created with system generated name

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:

1-- Example of a system-generated constraint name
2CREATE TABLE Employees (
3    EmployeeID INT PRIMARY KEY,
4    EmployeeName NVARCHAR(100) NOT NULL
5);
6-- 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.

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:

 1-- Step 1: Example to find the existing constraint
 2SELECT name 
 3FROM sys.objects 
 4WHERE object_id = OBJECT_ID(N'sys.default_constraints') 
 5AND parent_object_id = OBJECT_ID('Employees');
 6
 7-- Step 2: Drop system-generated constraint
 8ALTER TABLE Employees DROP CONSTRAINT PK__Employees__3214EC07DAE0CAD7;
 9
10-- Step 3: Add constraint with a meaningful name
11ALTER TABLE Employees ADD CONSTRAINT PK_EmployeeID PRIMARY KEY (EmployeeID);

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

Rule has no parameters.

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

Naming Rules, Bugs

There is no additional info for this rule.

Other Resources