The Pitfalls of Using System-Named Constraints in SQL Server

This article was published by Aaron Bertrand a few years ago, talking about system-named constraints in SQL Server.

The article mostly focuses on the issue of naming conventions as the main issue with system-named constraints and provides a useful stored procedure script to generate sp_rename commands for all system-named constraints.

However, the script in the article provides the solution for only one database and doesn't support the new "Edge Constraints" that were introduced in SQL Server 2019.

Without taking away from Aaron Bertrand's article, I'd like to expand on it and add a few more things.

System-named constraints

So what are system-named constraints?

I'll quote from Aaron Bertrand's article to answer this specific question:

In many systems, keys, indexes and constraints are given names generated by the system. These system-generated names relate somewhat to the objects they belong to, but often have some truncation of entity names as well as the addition of meaningless uniquifying strings Aaron Bertrand

Here are a list and explanation of all (currently supported) constraint types in SQL Server:

1. Primary keys: A primary key is a column or set of columns that uniquely identifies each row in a table. It ensures that the data in the table is unique and that there are no duplicates. Primary keys can also be involved in Foreign key relationships. Each table can have no more than one primary key.

2. Foreign keys: A foreign key is a column or set of columns that references a primary key in another table. It ensures that the data in the table is consistent with the data in the referenced table.

3. Unique constraints: A unique constraint is a constraint that ensures that the data in a column or set of columns is unique. It is similar to a primary key, but it does not necessarily enforce the requirement that the data in the column or set of columns be non-null. It's also possible to create a "Unique Index" without creating it as a constraint, and thus be able to have additional things such as INCLUDE columns and a FILTER expression. Also, unlike a primary key, you can create multiple unique constraints on the same table.

4. Default constraints: A default constraint is a constraint that specifies a default value for a column. If no value is specified for the column, the default value is used.

5. Check constraints: A check constraint is a constraint that specifies a condition that must be true for a column or set of columns. It can be used to enforce complex business rules that cannot be enforced by other types of constraints.

6. Edge constraints: An edge constraint is a constraint that is used to enforce relationships between nodes in a graph database. It ensures that only valid edges are created between nodes. Edge constraints are a new feature since SQL Server 2019.

Each of the above constraint types can optionally be created without specifying a name for it, which will cause SQL Server to auto-generate a name for it.

So what's the problem with system-named constraints?

When a table is created in SQL Server, system-named constraints are automatically generated based on a naming convention.

For example, The naming convention used by SQL Server for system-named Primary Key and Foreign Key constraints is as follows:

PK__<table>__<hexadecimal number>
FK__<table>_<referenced_table>__<hexadecimal number>

where <table> is the name of the table, <referenced_table> is the name of the referenced table in a foreign key constraint, and <hexadecimal number> is a random hexadecimal number generated by SQL Server.

Disadvantages of using system-named constraints:

1. Lack of meaningful names: System-named constraints use a naming convention that can be difficult to understand, especially if there are multiple constraints on a table. This can make it difficult to troubleshoot problems with the data in the database, and it can also make it difficult to understand the purpose of the constraint.

2. Difficulty in tracking changes: Since system-named constraints are generated automatically by SQL Server, it can be difficult to track changes to the constraints over time. This can make it difficult to understand how the database has evolved and to troubleshoot problems that arise.

3. Dependencies on constraint names: If system-named constraints are used in SQL scripts or stored procedures, any changes to the constraint name can cause errors and break dependencies. This can make it very difficult in development, especially if there are multiple developers working on the same database.

4. Non-aligned database environments: System-named constraints will be different every time they're created as they are randomly generated, which may cause the "same" constraint to be differently named in different environments (development, testing, QA, staging, production...). This can make it difficult to maintain and deploy the database in an automated fashion to multiple environments.

For example

Here's an example script for demonstrating the creation of a few tables with such system-named constraints:

DROP TABLE IF EXISTS [dbo].[TestTable2];
DROP TABLE IF EXISTS [dbo].[TestTable1];
DROP TABLE IF EXISTS dbo.TestEdgeTable;
DROP TABLE IF EXISTS dbo.TestNodeTable1;
DROP TABLE IF EXISTS dbo.TestNodeTable2;
DROP TABLE IF EXISTS dbo.TestNodeTable3;
GO
IF SCHEMA_ID('EitanTest') IS NOT NULL DROP SCHEMA EitanTest;
GO
CREATE SCHEMA EitanTest AUTHORIZATION dbo;
GO

CREATE TABLE EitanTest.[TestTable1](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[InsertTime] [datetime] NOT NULL DEFAULT (GETUTCDATE()),
	[Salary] [int] NOT NULL CHECK ([Salary]>=0),
	[Email] nvarchar(128) NOT NULL UNIQUE CHECK ([Email] LIKE N'_%@_%._%'),
	[ParentID] [int] NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1](ID),
	CHECK ([InsertTime] > '2000-01-01' AND [Salary] > 1)
);

CREATE TABLE [dbo].[TestTable2](
	[ID] [int] IDENTITY(1,1) NOT NULL PRIMARY KEY,
	[HeadID] [int] NOT NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1] ([ID]),
	[Email2] nvarchar(128) NULL FOREIGN KEY REFERENCES EitanTest.[TestTable1] ([Email])
);

-- If SQL 2019 or newer
IF (CONVERT(FLOAT, (@@microsoftversion / 0x1000000) & 0xff)) >= 15
BEGIN
	EXEC(N'
		-- CREATE node and edge tables
		CREATE TABLE dbo.TestNodeTable1
		(
		ID INTEGER PRIMARY KEY
		, CustomerName VARCHAR(100)
		)
		AS NODE;

		CREATE TABLE dbo.TestNodeTable2
		(
		ID INTEGER PRIMARY KEY
		, SupplierName VARCHAR(100)
		)
		AS NODE;

		CREATE TABLE dbo.TestNodeTable3
		(
		ID INTEGER PRIMARY KEY
		, ProductName VARCHAR(100)
		)
		AS NODE;

		-- CREATE edge table with edge constraints.
		CREATE TABLE dbo.TestEdgeTable
		(
		PurchaseCount INT
		, CONNECTION (dbo.TestNodeTable1 TO dbo.TestNodeTable3, dbo.TestNodeTable2 TO dbo.TestNodeTable3)
		, CONNECTION (dbo.TestNodeTable2 TO dbo.TestNodeTable1)
		)
		AS EDGE;'
	);
END

There are two tables created by this script - TestTable1 and TestTable2, each with various system-named constraints created on them. If you're using SQL Server version 2019 or newer, the script will also create 4 more tables to demonstrate the new Edge constraints as well.

If you run the exact same script above in two different databases, you'll get constraints with entirely different names in each database.

What if one such database is your "production" database, and the other is your "testing" or "development" database?

What if you're doing some work on the "lower environment" and then want to do a SQL Schema Compare with the "production environment" to see which changes you want to deploy, but those pesky system-named constraints always show up and interfere even though they may not actually be "different"?

Also, if you have some kind of database change automation set up (like a CI/CD release pipeline), you may encounter a situation where your constraints keep getting dropped and re-created because the automated deployment tool keeps thinking that the constraints are different but then re-creates them with a system-defined name again (and the system-defined name will be different every time).

Generating standardized constraint names:

To avoid the issues associated with system-named constraints, it can be helpful to use a TSQL script to generate standardized constraint names. The script below can automatically generate meaningful names for all system-named constraints in all databases. This can make it easier to understand the purpose of each constraint, it can make it easier to track changes to the constraints over time, and it can make it easier to align different database environments, and reduce issues related to change management. Also, the fact that this script generates the output for all accessible databases makes it much easier for enterprise-level environments with a lot of databases.

If you want, the script can be run on a regular basis to ensure that all new constraints are named consistently.

The script is available in our Madeira Toolbox on GitHub:

• generate_rename_system_named_constraints.sql

Simply run the script and then copy & paste the entire remediationCommand column to get the script(s) to rename the constraints.

The constraint types supported by this script with their prefixes and naming convention:

Short type name	Full type name	Naming convention
DF	Default Constraints	DF_[Non-dbo-SchemaName_]TableName_ColumnName
PK	Primary Keys	PK_[Non-dbo-SchemaName_]TableName
UQ	Unique Constraints	UQ_[Non-dbo-SchemaName_]TableName_ColumnName(s)
FK	Foreign Keys	FK_[Non-dbo-SchemaName_]TableName[_ColumnName(s)]_ReferencedTable
CK	Check Constraints	CK_[Non-dbo-SchemaName_]TableName[_ColumnName]
EC	Edge Constraints (SQL Server 2019 and newer)	EC_TableName

Examples:

• DF_TestTable_InsertTime

• PK_TestTable

• UQ_TestTable_Email

• FK_TestTable2_TestTable1

• CK_TestTable_Salary

• EC_TestEdgeTable

Running the script in the database after running the example creation script above will generate an output such as below:

Arguments

@FilterByDatabase	sysname	= NULL

Optionally filter by a specific database name. Leave as NULL to check all accessible databases.

@IncludeColumnsInFK	bit	= 0	

Optionally set to 1 to include referencing column names in foreign key constraint names.

For example, with @IncludeColumnsInFK set to 0:

FK_TestTable1_TestTable1

With @IncludeColumnsInFK set to 1:

FK_TestTable1_ParentID_TestTable1

@IncludeNonDBOSchema	bit	= 1

Optionally set to 1 to include non-dbo schema names in the constraint names.

This could be useful to avoid duplicate constraint names in different schemas.

Note that you can potentially have "duplicate" names due to a combination of schema names, table names, and column names, especially if you have underscores in your object names. However, the script takes this into consideration and will automatically add an underscore and a number next to each such constraint name, to avoid duplicates.

You may want to review these names regardless, to make sure that they remain readable and understandable to you and your peers, to avoid confusion.

@SystemNamedOnly	bit	= 1

Optionally set to 0 to include user-named constraints as well (for standardizing all constraint names).

Supported SQL Server Versions

This script uses the CONCAT function which is supported only in SQL Server 2012 and newer.

Conclusion:

In conclusion, while system-named constraints can be useful, they have some disadvantages, especially in the context of source control, version control, and automated deployments. They can be difficult to understand and maintain, and they can cause issues with dependencies and tracking changes. To mitigate these issues, it is often desirable to use user-named constraints or to generate meaningful names for system-named constraints using something like the script provided above.