Understanding DEFAULT constraints in MS SQL Server

If you have worked with multiple instances of a database, there is a high chance that you have faced issues with constraints default names. You generated the script for tables/schema changes and try to run on another instance of that database, where some constraint is being dropped from a table, and you get error message.

For example, you have an Items table, have a constraint defined on column Item_Code with default name, and a drop constraint statement like this:

 ALTER table [Items] drop DF__Items__Item_Code__07F6335B

It may worked on your current database instance, but on some other instances it may give you an error message like this:

 Msg 3728, Level 16, State 1, Line 1
 'DF__Items__Item_Code__07F6335B' is not a constraint.
 Msg 3727, Level 16, State 0, Line 1
 Could not drop constraint. See previous errors.

Why you get this error?

Because at the time you created this constraint, you have not provided a custom name, so SQL Server generated a default name. Since default name is auto-generated, there are no chances that this same name will be generated on each instance, no all other instances are having constraint with same name. When you try to run this drop constraint statement on other instances, you are getting this error.

Lets try to understand this with an example.

Create a new database.

Create a new table, lets say Items with this statement.

CREATE TABLE [dbo].[Items](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Item_Code] [nvarchar](255) NOT NULL,
 [Item_Name] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Items] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

At this point we have no constraints on any column of Items table. Lets defined a new one.

 ALTER TABLE [dbo].[Items] ADD  DEFAULT ('') FOR [Item_Code]
 GO

In this constraint, we are setting empty string as default value for column Item_Code. I did not provide any name, SQL Server has defined an auto-generated name, in my case it is DF__Items__Item_Code__07F6335A, and this is not a good approach.

Better is to define a custom name for constraints, which will remain static across all instances of this database.

Lets define a new constraint on Item_Name column, but this time with custom name:

 ALTER TABLE [dbo].[Items] ADD  CONSTRAINT [DF_Items_Item_Name]  DEFAULT ('') FOR [Item_Name]
 GO

Here constraint name is DF_Items_Item_Name, and will remain common on all nodes.

So far, we have created a new table and then defined constraints on existing columns.

The same problem will happen when you add new columns to this table. Lets add a new column CreatedBy with default name 'admin':

 ALTER TABLE [dbo].[Items] ADD [CreatedBy] [nvarchar](20) NOT NULL DEFAULT 'admin' 
 GO    

Here again, we did not provide a custom name, so SQL Server will place auto-generated. In my case it is DF__Items__CreatedBy__0DAF0CB0.

Good technique will be to provide custom name, lets create a new column CreatedOn, but this time with our specified name:

 ALTER TABLE [dbo].[Items] ADD [CreatedOn] [datetime] 
  CONSTRAINT [DF_Items_CreatedOn] DEFAULT (getdate()) NOT NULL 
 GO

Here constraint name is DF_Items_CreatedOn.

One thing to be aware that you can not change constraint names, you have to drop old one and create new constraint if you need to modify or rename.

I hope you found this post helpful. Share your thoughts in the comments below!