Imagine you’re a developer working on an OLTP system.
You add a quick check constraint to enforce valid values for a column — nothing fancy, just a simple rule to keep bad data out. It passes tests, goes through code review, and ships to production.
A few months later, the requirements change. The business logic expands overnight. Your company’s policy is strict: any change script must be tested in development first, then run exactly as-is in production.
So you write your ALTER TABLE ... DROP CONSTRAINT statement — but what’s the name of the constraint in production? You never named it. SQL Server did. And it probably generated something like CK__Orders__Status__5AEE82B9. Good luck guessing that from dev, where it’s a different random hash altogether.
Now you have two options:
- Query the system catalog manually, adjust the script by hand, hope you don’t mistype it, then explain why the dev and prod scripts aren’t identical.
- Or, if you’d named it in the first place —
CK_Orders_Status— you’d have one clean, portable, predictable script that works every time.
This is why I always name my constraints — every primary key, unique key, check, default, and foreign key. It’s a tiny habit that prevents hours of confusion and unnecessary risk later on.
How Unnamed Constraints Happen
By default, if you don’t provide a name, SQL Server generates one for you, but it’s random and unpredictable.
Avoid the syntax like below. That is how unnamed constraints are introduced into your database.
CREATE TABLE [dbo].[Order]
(
[Id] INT NOT NULL PRIMARY KEY,
[OrderNumber] VARCHAR(50) NOT NULL UNIQUE,
[Status] VARCHAR(20) NOT NULL
CHECK ([Status] IN ('New', 'Shipped', 'Cancelled')),
[OrderedAt] DATETIME2(2) DEFAULT (SYSUTCDATETIME()),
[CustomerId] INT,
FOREIGN KEY ([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
);Practical Syntax — All Constraints, Named
Use the syntax below to define named constraints directly when creating the table.
CREATE TABLE [dbo].[Order]
(
[Id] INT NOT NULL CONSTRAINT [PK_Order] PRIMARY KEY,
[OrderNumber] VARCHAR(50) NOT NULL,
[Status] VARCHAR(20) NOT NULL,
[OrderedAt] DATETIME2(2) CONSTRAINT [DF_Order_OrderedAt]
DEFAULT (SYSUTCDATETIME()),
[CustomerId] INT,
CONSTRAINT [UQ_Order_OrderNumber]
UNIQUE ([OrderNumber]),
CONSTRAINT [CK_Order_Status]
CHECK ([Status] IN ('New', 'Shipped', 'Cancelled')),
CONSTRAINT [FK_Order_Customer] FOREIGN KEY ([CustomerId])
REFERENCES [dbo].[Customer] ([Id])
);
You can use the following syntax to define named constraints after creating a table.
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [PK_Order]
PRIMARY KEY ([Id]);
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [UQ_Order_OrderNumber]
UNIQUE ([OrderNumber]);
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [CK_Order_Status]
CHECK ([Status] IN ('New', 'Shipped', 'Cancelled'));
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [DF_Order_OrderedAt]
DEFAULT (SYSUTCDATETIME()) FOR [OrderedAt];
ALTER TABLE [dbo].[Order] ADD CONSTRAINT [FK_Order_Customer]
FOREIGN KEY ([CustomerId]) REFERENCES [dbo].[Customer] ([Id]);How to identify unnamed constraints
You can use the following script to list all unnamed constraints in all user databases on your SQL Server instance.
/*
================================================================================
Script: FindUnnamedConstraints_AllDatabases.sql
Purpose: List all system-named (unnamed) constraints across ALL user
databases on this SQL Server instance. Helps ensure consistent
naming conventions for Primary Keys, Unique, Check, Default,
and Foreign Key constraints.
Author: Ryszard Kozlowski
Website: https://www.ittiger.net/
Usage:
1. Run this script in the context of the master database (or any DB).
2. It will scan all user databases (excluding system DBs).
3. It outputs a list showing:
- Database name
- Schema
- Table name
- Constraint type (PK, UQ, C, D, F)
- Actual constraint name
4. Use this list to decide which constraints need renaming.
Notes:
- No data or constraints are changed.
- Safe to run multiple times.
================================================================================
*/
SET NOCOUNT ON;
-- Clean up temp tables if they exist
DROP TABLE IF EXISTS #Databases;
DROP TABLE IF EXISTS #Constraints;
-- Table to store user database names
CREATE TABLE #Databases
(
Id INT NOT NULL PRIMARY KEY,
Name NVARCHAR(128) NOT NULL UNIQUE
);
-- Table to store found unnamed constraints
CREATE TABLE #Constraints
(
Id INT IDENTITY(1,1) PRIMARY KEY,
DatabaseName NVARCHAR(128),
TableSchema NVARCHAR(128),
TableName NVARCHAR(128),
ConstraintType CHAR(2),
ConstraintName NVARCHAR(128),
UNIQUE (DatabaseName, TableSchema, TableName, ConstraintType, ConstraintName)
);
-- Populate user database list (excluding system DBs)
INSERT INTO #Databases (Id, Name)
SELECT database_id, name
FROM sys.databases
WHERE database_id > 4 -- skip master, tempdb, model, msdb
AND state_desc = 'ONLINE';
-- Loop variables
DECLARE @DbId INT = (SELECT MIN(Id) FROM #Databases);
DECLARE @DbName NVARCHAR(128);
DECLARE @Sql NVARCHAR(MAX);
-- Core query to find unnamed constraints in the current DB
DECLARE @QueryTemplate NVARCHAR(MAX) = N'
SELECT
DB_NAME() AS [DatabaseName],
SCHEMA_NAME(t.schema_id) AS [TableSchema],
t.name AS [TableName],
c.type AS [ConstraintType],
c.name AS [ConstraintName]
FROM
sys.objects AS c
JOIN sys.tables AS t ON t.object_id = c.parent_object_id
LEFT JOIN sys.default_constraints dc ON dc.object_id = c.object_id AND dc.is_system_named = 1
LEFT JOIN sys.check_constraints cc ON cc.object_id = c.object_id AND cc.is_system_named = 1
LEFT JOIN sys.foreign_keys fk ON fk.object_id = c.object_id AND fk.is_system_named = 1
LEFT JOIN sys.key_constraints k ON k.object_id = c.object_id AND k.is_system_named = 1
WHERE
c.type IN (''PK'', ''UQ'', ''C'', ''D'', ''F'')
AND (
dc.is_system_named = 1
OR cc.is_system_named = 1
OR fk.is_system_named = 1
OR k.is_system_named = 1
)
ORDER BY
SCHEMA_NAME(t.schema_id), t.name, c.type;';
-- Loop through each user DB
WHILE @DbId IS NOT NULL
BEGIN
SELECT @DbName = Name FROM #Databases WHERE Id = @DbId;
SET @Sql = N'USE ' + QUOTENAME(@DbName) + '; ' + @QueryTemplate;
INSERT INTO #Constraints (DatabaseName, TableSchema, TableName, ConstraintType, ConstraintName)
EXEC (@Sql);
-- Next DB
SELECT @DbId = MIN(Id) FROM #Databases WHERE Id > @DbId;
END;
-- Final output
SELECT
DatabaseName,
TableSchema,
TableName,
ConstraintType,
ConstraintName
FROM #Constraints
ORDER BY
DatabaseName,
TableSchema,
TableName,
ConstraintType,
ConstraintName;
-- Cleanup
DROP TABLE IF EXISTS #Databases;
DROP TABLE IF EXISTS #Constraints;
How to rename unnamed constraints
You can use the following script to generate a rename statement for an unnamed constraint in your current database.
Note: This script does not rename constraints automatically. It only generates a list of sp_rename statements that you can — and should — review carefully before execution.
/*
================================================================================
Script: GenerateRenameStatements_UnnamedConstraints.sql
Purpose: In the current database, find all constraints that were
system-named (unnamed by the developer) and generate
`sp_rename` statements to rename them according to a
consistent convention.
Constraint types:
- PK (Primary Key)
- UQ (Unique Constraint)
- CK (Check Constraint)
- DF (Default Constraint)
- FK (Foreign Key)
Behavior:
- This script DOES NOT rename constraints automatically.
- It only prints recommended `sp_rename` commands for review.
- Safe to run multiple times.
Author: Ryszard Kozlowski
Website: https://www.ittiger.net/
Compatibility:
- Works on older SQL Server versions (no STRING_AGG)
================================================================================
*/
SET NOCOUNT ON;
-- Clean up temp table if it exists
DROP TABLE IF EXISTS #RenameStatements;
CREATE TABLE #RenameStatements
(
Id INT IDENTITY(1,1) PRIMARY KEY,
RenameStatement NVARCHAR(MAX)
);
-- Insert sp_rename commands for all system-named constraints in the current DB
INSERT INTO #RenameStatements (RenameStatement)
SELECT
'EXEC sp_rename N'''
+ QUOTENAME(OBJECT_SCHEMA_NAME(c.parent_object_id)) + '.' + QUOTENAME(c.name) + ''', N'''
+
CASE c.type
WHEN 'PK' THEN 'PK_' + t.name
WHEN 'UQ' THEN 'UQ_' + t.name + '_' + STUFF((
SELECT '_' + c2.name
FROM sys.index_columns ic
JOIN sys.columns c2
ON c2.object_id = ic.object_id AND c2.column_id = ic.column_id
WHERE ic.object_id = i.object_id AND ic.index_id = i.index_id
ORDER BY ic.key_ordinal
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')
WHEN 'C' THEN 'CK_' + t.name + '_' + col.name
WHEN 'D' THEN 'DF_' + t.name + '_' + col.name
WHEN 'F' THEN 'FK_' + t.name + '_' + rt.name
ELSE 'UNKNOWN_' + c.name
END + ''', ''OBJECT'';'
FROM
sys.objects AS c
JOIN sys.tables AS t ON t.object_id = c.parent_object_id
LEFT JOIN sys.default_constraints dc ON dc.object_id = c.object_id AND dc.is_system_named = 1
LEFT JOIN sys.check_constraints cc ON cc.object_id = c.object_id AND cc.is_system_named = 1
LEFT JOIN sys.foreign_keys fk ON fk.object_id = c.object_id AND fk.is_system_named = 1
LEFT JOIN sys.key_constraints k ON k.object_id = c.object_id AND k.is_system_named = 1
LEFT JOIN sys.indexes i ON i.object_id = t.object_id AND i.name = c.name
LEFT JOIN sys.columns col
ON col.object_id = t.object_id
AND (
(dc.parent_column_id = col.column_id) OR
(cc.parent_column_id = col.column_id)
)
LEFT JOIN sys.foreign_keys f ON f.object_id = c.object_id
LEFT JOIN sys.tables rt ON rt.object_id = f.referenced_object_id
WHERE
c.type IN ('PK','UQ','C','D','F')
AND (
dc.is_system_named = 1
OR cc.is_system_named = 1
OR fk.is_system_named = 1
OR k.is_system_named = 1
)
ORDER BY t.name, c.type;
-- Output sp_rename statements for review
SELECT RenameStatement
FROM #RenameStatements
ORDER BY Id;
-- Clean up
DROP TABLE IF EXISTS #RenameStatements;
Bottom line
Naming your constraints is a small habit with a big impact. It keeps your deployments predictable and your scripts portable — exactly how robust systems should be.