Did You Really Name That Default?

Ten years (and a couple jobs) ago, I wrote about naming default constraints to avoid having SQL Server name them for you. I closed with the following statement:

SQL Server needs a name for the constraint regardless; it’s worth specifying it yourself.

We’re back with a new wrinkle in the story.

Consider the following table:

1
2
3
4
5

CREATE TABLE [dbo].[MyTable] (
		[ID] INT identity NOT NULL PRIMARY KEY CLUSTERED
		,[FirstName] NVARCHAR(100) NOT NULL CONSTRAINT [DF_MyTable_FirstName] DEFAULT('Andy')
		,[LastName] NVARCHAR(100) CONSTRAINT [DF_MyTable_LastName] NOT NULL DEFAULT('Levy')
		);

Both of my default constraints are named, right? Let’s take a look.

1
2
3
4
5
6
7
8
9

SELECT DF.[name] AS [ConstraintName]
	,DF.[is_system_named] AS [IsSystemNamed]
	,S.[name] AS [SchemaName]
	,object_name(DF.[parent_object_id]) AS [TableName]
	,C.[name] AS [ColumnName]
FROM sys.default_constraints AS DF
JOIN sys.schemas AS S ON S.[schema_id] = DF.[schema_id]
JOIN sys.columns AS C ON C.[column_id] = DF.[parent_column_id]
	AND C.[object_id] = DF.[parent_object_id];

Wait. I named that constraint. It’s right there in my CREATE TABLE. Why is it system-named? Do you see where I went wrong?

• NOT NULL CONSTRAINT [DF_MyTable_FirstName] DEFAULT('Andy') - The constraint is named
• CONSTRAINT [DF_MyTable_LastName] NOT NULL DEFAULT('Levy') - The constraint is system-named

These are both valid T-SQL (else I wouldn’t have been able to create the table at all). But they behave differently. Where did my constraint name go? To tell you the truth - I have no idea. I’m pretty sure that doesn’t exist at all. I can’t find that name in sys.all_objects or anywhere else. The constraint itself exists, but only with a system-generated name. If I ask SQL Server Management Studio to script out the table, it gives me the both constraints, but only one has a name that I specified in my original statement.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16

CREATE TABLE [dbo].[MyTable](
	[ID] [int] IDENTITY(1,1) NOT NULL,
	[FirstName] [nvarchar](100) NOT NULL,
	[LastName] [nvarchar](100) NOT NULL,
PRIMARY KEY CLUSTERED 
(
	[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[MyTable] ADD  CONSTRAINT [DF_MyTable_FirstName]  DEFAULT ('Andy') FOR [FirstName]
GO

ALTER TABLE [dbo].[MyTable] ADD  DEFAULT ('Levy') FOR [LastName]
GO

This means that if I decide to script out the database at a later time to put it into source control (let’s pretend that I didn’t do that when I first started building the database), it won’t ever get the name I originally wanted and thought I had given it.

Where do we go from here? If naming constraints matters to you, start by building good habits when writing your CREATE TABLE and ALTER TABLE statements, using the same pattern and order of arguments/keywords. If you’re using a linter or static analyzer, create a rule to specifically check the ordering in these statements and flag the CONSTRAINT <name> NOT NULL DEFAULT syntax at the same time you have it check for the presence of a name. And be on the lookout in your code reviews too!