SA0069B : Check all Default Constraints in the current script for following specified naming convention

Inconsistent or unclear default constraint naming can lead to confusion and maintenance challenges.

Description

In SQL Server, default constraints are often used in CREATE TABLE and ALTER TABLE statements to specify what value should be inserted in a column if no value is provided. While these constraints are functional, improper or inconsistent naming of default constraints can lead to confusion and maintenance challenges.

For example:

SQL
Copy
1-- Example of a default constraint without a clear naming pattern
2CREATE TABLE Orders (
3    OrderID int PRIMARY KEY,
4    OrderDate datetime DEFAULT GETDATE()
5);

In this example, the default constraint for OrderDate has no explicit name. This can make future changes and debugging difficult, as the constraint must be referenced by an automatically generated name, which is often not intuitive.

  • Lack of a consistent naming convention makes understanding and managing constraints across tables more complex.

  • Automated tools and scripts may struggle to identify constraints when names do not follow a predictable pattern.

How to fix

Ensure default constraints in SQL Server adhere to a consistent naming convention for better maintainability and clarity.

Follow these steps to address the issue:

  1. Identify any default constraints without a clear or consistent naming pattern. You can query the system views to find constraints without meaningful names:

  2. Use sp_rename to assign a meaningful name to the existing default constraint. The naming convention could include the table and column names for clarity.

  3. Ensure that future constraints are named according to the agreed convention during the CREATE TABLE or ALTER TABLE operations.

For example:

SQL
Copy
 1-- Find existing default constraints
 2SELECT 
 3    OBJECT_NAME(c.object_id) AS TableName,
 4    c.name AS ConstraintName,
 5    COL_NAME(c.parent_object_id, c.parent_column_id) AS ColumnName
 6FROM sys.default_constraints AS c;
 7
 8-- Rename a default constraint
 9EXEC sp_rename 
10    'DF_OldConstraintName', 
11    'DF_Orders_OrderDate', 
12    'OBJECT';
13
14-- Create a new table with a properly named default constraint
15CREATE TABLE Orders (
16    OrderID int PRIMARY KEY,
17    OrderDate datetime CONSTRAINT DF_Orders_OrderDate DEFAULT GETDATE()
18);

Scope

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

Parameters
Name Description Default Value
NamePattern

Deault constraint name pattern.

DF_{table_name}_{column_name}
Remarks

The rule does not need Analysis Context or SQL Connection.

Effort To Fix
8 minutes per issue.
Categories

Naming Rules, Code Smells

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
Copy
 1CREATE TABLE Orders (
 2    ID int NOT NULL,
 3    OrderNumber int NOT NULL,
 4    OrderDate date CONSTRAINT DF_DefaultDate DEFAULT GETDATE()
 5);
 6
 7CREATE TABLE Orders (
 8    ID int NOT NULL,
 9    OrderNumber int NOT NULL CONSTRAINT DF_DefaultNumber DEFAULT 1,
10    OrderDate date CONSTRAINT DF_DefaultDate DEFAULT GETDATE()
11);
12
13ALTER TABLE Persons 
14ADD OrderDate date NOT NULL CONSTRAINT DF_DefaultDate DEFAULT GETDATE();
Analysis Results
  Message Line Column
1 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Orders_OrderDate]. 4 30
2 SA0069B : The default constraint name DF_DefaultNumber does not match the naming convention. The expected default constraint name is [DF_Orders_OrderNumber]. 9 40
3 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Orders_OrderDate]. 10 30
4 SA0069B : The default constraint name DF_DefaultDate does not match the naming convention. The expected default constraint name is [DF_Persons_OrderDate]. 14 39
See Also

Other Resources

Analysis Rules