Name Your Defaults So SQL Server Doesn't

Something in SQL Server that isn’t always obvious to beginners is that when you create a default value for a column on a table, SQL Server creates a constraint (much like a primary or foreign key). All constraints must have a name, and if one isn’t specified SQL Server will generate one for you. For example:

1
2
3
4
5

CREATE TABLE [dbo].[point_types] (
   [typeid] [int] NOT NULL DEFAULT(NEXT VALUE FOR [pointtypeid])
  ,[typename] [nvarchar](30) NOT NULL DEFAULT 'Unspecified'
  ,CONSTRAINT [PK_PointType] PRIMARY KEY CLUSTERED ([typeid] ASC)
)

We’ve got a simple table here and both fields have a default value set (the primary key’s value is generated from a sequence object, pointtypeid). Time goes on, and a change in policy comes up which requires that I change the default value of typename to Unknown. To do this, I have to drop the constraint and re-create it. To find the name of the constraint, I can either ask sp_help, or run this query:

1
2
3
4
5
6
7
8
9

SELECT all_columns.NAME
 ,default_constraints.NAME
 ,default_constraints.DEFINITION
FROM sys.all_columns
  INNER JOIN sys.tables ON all_columns.object_id = tables.object_id
  INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
  INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id
WHERE schemas.NAME = 'dbo'
  AND tables.NAME = 'point_types';

I’ve got my constraint name now, so I can drop it & re-create it

1
2
3
4
5

ALTER TABLE [dbo].[point_types]

DROP CONSTRAINT DF__point_typ__typen__21B6055D; GO

ALTER TABLE [dbo].[point_types] ADD DEFAULT('Unknown') FOR [typename]; GO 

And if I re-run the above query, I can see that the constraint’s name is different.

This means that everywhere I need to change this constraint (development, test and production), I’ll need to figure out the constraint name in that particular database and drop it before re-creating it. This makes a deployment script a bit messier, as it needs more code to find those constraint names

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12

DECLARE @constraintname VARCHAR(50);

SELECT @constraintname = default_constraints.NAME
FROM sys.all_columns
INNER JOIN sys.tables ON all_columns.object_id = tables.object_id
INNER JOIN sys.schemas ON tables.schema_id = schemas.schema_id
INNER JOIN sys.default_constraints ON all_columns.default_object_id = default_constraints.object_id
WHERE schemas.NAME = 'dbo' AND tables.NAME = 'point_types';

DECLARE @sql NVARCHAR(200) = N'alter table [dbo].[point_types] drop constraint ' + @constraintname;

PRINT @sql;

1
2
3
4

EXECUTE sp_executesql @sql;

ALTER TABLE [dbo].[point_types] ADD DEFAULT('Unknown') FOR [typename];
GO

But this doesn’t really solve my problem, it just works around it. It’s still messy and fragile. If I need to do other operations on the default constraint, I need to go through the same exercise to find its name.

Fortunately, SQL Server lets us name default constraints just like any other constraint, and by doing so we avoid this trouble. By setting my own name for the constraint, I know what it’ll be in every database, without having to query system tables. The name can be set in both the CREATE TABLE statement and an independent ALTER TABLE.

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

CREATE TABLE [dbo].[point_types] (
   [typeid] [int] NOT NULL DEFAULT(NEXT VALUE FOR [pointtypeid])
   ,[typename] [nvarchar](30) NOT NULL
   CONSTRAINT [DF_PT_TypeName] DEFAULT 'Unspecified'
   ,CONSTRAINT [PK_PointType] PRIMARY KEY CLUSTERED ([typeid] ASC)
);

GO

ALTER TABLE [dbo].[point_types] DROP CONSTRAINT [DF_PT_TypeName];

GO

ALTER TABLE [dbo].[point_types] ADD CONSTRAINT [DF_PT_TypeName] DEFAULT('Unknown') FOR [typename];
GO

I can also combine these in the next deployment that requires a change to the default constraint, dropping the system-generated name and establishing my own static name to make things simpler in the future.

Is explicitly naming default (or any other) constraints necessary? No, but doing so helps your database document itself, and it makes future deployment/promotion scripts simpler and less prone to breakage. SQL Server needs a name for the constraint regardless; it’s worth specifying it yourself.