Working with DEFAULT constraints in SQL Server

By:   |   Comments (10)   |   Related: > Constraints


I have a DEFAULT defined on a table column and I have to create a script to update this DEFAULT to reflect a change in business requirements. I tried an ALTER TABLE statement, but that generates a syntax error. How can I easily redefine DEFAULTs?


The ANSI SQL standard defines DEFAULT as being a column attribute whereas Microsoft implemented DEFAULT as a type of constraint. As a result, you cannot change a DEFAULT using ALTER TABLE...ALTER COLUMN syntax. Instead, you must drop the DEFAULT as you would other constraint objects (such as FOREIGN KEYS, PRIMARY KEYS, etc) and re-add the DEFAULT constraint with the new default value.

The following example creates a new table with a named constraint and later updates the constraint

create table dbo.CustomerReport 
customer_id int, 
report_id int, 
rundate datetime not null 
constraint df_CustomerReport_rundate default getdate() 

exec sp_helpconstraint [dbo.CustomerReport]
fig 1

Now that the table is built, let's assume that our run dates now need to be stored in UTC time as opposed to the local server time. Let's attempt to alter the default for the rundate from getdate() to getutcdate():  

alter table dbo.CustomerReport alter column rundate default getutcdate() 
Fig 2

As you can see, we receive a syntax error. To make the change, we need to DROP the constraint and then re-add it: 

alter table dbo.CustomerReport 
drop constraint df_CustomerReport_rundate
alter table dbo.CustomerReport 
add constraint df_CustomerReport_rundate default getutcdate() for rundate
exec sp_helpconstraint [dbo.CustomerReport]
Fig 3

This is all well and good if your DEFAULT constraints are named, but this may not always be the case (in fact, in my experience, this is rarely the case). If a DEFAULT is defined inline within a table without a name, a unique name will be auto-generated by the SQL Server database engine. This unique name is a bit ugly and hard work with. If you maintain multiple databases, this auto-generated name will be different from database to database. As a result, you cannot rely on using an an auto-generated name statically within a static DROP CONSTRAINT statement. In these cases, the only way to modify DEFAULT constraints with auto-generated names is to capture the name from SQL Server's system catalog and to run a dynamic SQL query to perform the DROP.

You should note that SQL Server's INFORMATION_SCHEMA views include views that report on database constraints, however DEFAULTs cannot be found within them. As mentioned earlier, the ANSI-SQL standard defines constraints as a column property. Since the INFORMATION_SCHEMA views adhere to ANSI-SQL standards and DEFAULTs are not an ANSI-SQL defined type of constraint, they're not included within these views. However, you can easily find information about a column's DEFAULT constraint within the following system table catalogs:

  • SQL Server 2000: syscolumns
  • SQL Server 2005: sys.default_constraints

The following example re-creates our CustomerReport table with SQL Server 2005 auto-generating the constraint name. It then modifies the DEFAULT while giving it a user-friendly, and more importantly, a consistent name.

First, let's re-create the example table and examine the generated constraint name

drop table dbo.CustomerReport 
create table dbo.CustomerReport 
customer_id int, 
report_id int, 
rundate datetime not null default getdate()

exec sp_helpconstraint [dbo.CustomerReport]
Fig 4

Now, let's redefine the DEFAULT to use the UTC date:

declare @default_name varchar(256)
select @default_name = [name] from sys.default_constraints 
where parent_object_id = object_id('CustomerReport')
and col_name(parent_object_id, parent_column_id) = 'rundate'

exec('alter table dbo.CustomerReport drop constraint ' + @default_name)

alter table dbo.CustomerReport 
add constraint df_CustomerReport_rundate default getutcdate() for rundate

exec sp_helpconstraint [dbo.CustomerReport]
Fig 5

The SQL Server 2000 specific query to get the same column default name would look like this: 

-- SQL Server 2000 equivalent
select object_name(cdefault) as 'DefaultName' from syscolumns
where [id] = object_id('CustomerReport')
and [name] = 'rundate'
Fig 6

One thing to be aware of is that since DEFAULTs are implemented as constraints, any need to change a column's datatype or need to drop the column entirely will require a DROP of any DEFAULT constraint on the column prior to your action.

My recommendation is to use INFORMATION_SCHEMA views whenever possible when working with database objects and to avoid reading system tables whenever you can. My reasoning is that the SQL Server system catalogs can be (and have been) changed from release to release. In addition, some of these catalogs could be removed entirely from SQL Server. In SQL Server 2005, some of the well known SQL Server 2000 system catalog tables such as sysobjects and sysindexes are kept around as backward compatible views, but they may be gone by the time SQL Server 2008 is finally released. In some cases, reading the system catalogs is unavoidable. In these cases, make sure you document where you're reading system tables directly. Furthermore, I also recommend naming ALL database objects regardless if they're PRIMARY KEY, UNIQUE, CHECK, DEFAULT, etc. Explicitly naming all database objects eliminates the need to read system catalogs if you someday need to update these objects and also better documents your database.

Next Steps
  • Examine your data model for auto-generated constraint names and consider renaming them using some naming convention
  • Read more about the various INFORMATION_SCHEMA views and their resultsets in the SQL Server 2000 and 2005 Books Online
  • Read more about the COL_NAME() function in the SQL Server Books Online

sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip

About the author
MSSQLTips author Armando Prato Armando Prato has close to 30 years of industry experience and has been working with SQL Server since version 6.5.

This author pledges the content of this article is based on professional experience and not AI generated.

View all my tips

Comments For This Article

Wednesday, April 15, 2020 - 10:40:41 AM - Dev Back To Top (85377)

Helped me a lot. Very nicely described.

Tuesday, February 5, 2013 - 7:28:18 AM - Hiren Khirsaria Back To Top (21920)

Very Useful Article...!!!!


Thursday, November 29, 2012 - 11:32:24 AM - CodePro Back To Top (20636)

You will put business rule default constraints on my tables over my dead body.

Monday, August 6, 2012 - 9:22:57 AM - Armando Prato Back To Top (18932)

Manoj, I'm pleased to hear that it helped someone.  That's always been my goal. 

Monday, August 6, 2012 - 7:12:28 AM - Manoj Kumar Sharma Back To Top (18926)

Hello Armando,

Thank you so, so, so much for putting up this blog post.  I have been searching for this for the past 3 hours and at long last, found your blog post.  The explanation and the sample has been perfect.  Most people only talk of either adding a constraint to an existing column or talk of adding a column with a constraint.  Your solution has helped me to resolve an issue with over 500 databases, where I needed to change the default value I had specified as part of the table creation script, and had the autogenerated constraint name.

Just wanted to thank you for this help.

By the way, one small suggestion.  If you could change the title of your posting to read "Change Default Constraint of SQL Server Column" or something to that effect, the search engine(s) would be able to list your article more accurately.  I found your article when I searched for "find constraint name in sql server" on google.

Please keep up the good work, and sharing your knowledge.

Monday, May 21, 2012 - 3:05:38 PM - Burak Back To Top (17572)

thank you for suggestions

Monday, April 16, 2012 - 11:51:26 AM - Dhanasekar Back To Top (16941)

Hi Dave,


I have a query where I need to create a column say Status.

It is a Not-Null column. The table I m gonna add this column, already have some data in it.


So, wen creating the column I gave a Default value as 1.


Now after addition of this column, in the same script file, I dropped the default constraint. It throws like 'Status' column not found.


Please help me out


Saturday, March 6, 2010 - 5:00:33 PM - Hamburg Escort Back To Top (5024)
Thanks for sharing!

Saturday, June 6, 2009 - 3:43:53 PM - kanwarnasir Back To Top (3516)

Hello Admin!

I came across your article while searching the Internet to batch rename default-constraint names. Your article was a great source to accomplish my task. Thank you!

Based on your article, I created following T-SQL to batch rename default-constraints. I hope some folks find it useful.

-- ========================================
-- Purpose: T-SQL to rename default constraint names using following syntax
--   df_<tablename>_<columnname>
-- Author: Kanwar Nasir
-- ========================================

-- *************************************************
-- Change the database before executing this SQL --

USE [YourDBNameHere]

DECLARE @SQL nvarchar(4000),
  @TableName sysname,
  @ColumnName sysname,
  @DefaultConstraint varchar(256),
  @Definition varchar(256),
  @SchemaName sysname,
  @Return int
-- Cursor of all default constraints in the current database
SELECT object_name(d.parent_object_id) TableName, col_name(parent_object_id, parent_column_id) ColumnName,, schema_name

(d.schema_id), definition
FROM sys.default_constraints d INNER JOIN sys.columns c ON d.parent_column_id = c.column_id
WHERE d.is_system_named = 0
OPEN cursObjects
-- get the first row
 INTO @TableName, @ColumnName, @DefaultConstraint, @SchemaName, @Definition
-- Set the return code to 0
SET @Return = 0
-- start a transaction

-- Cycle through the rows of the cursor to change default constraint name
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
  -- Drop current constraint
  SET @SQL = 'alter table '+@SchemaName+'.'+@TableName+' drop constraint ' + @DefaultConstraint
  EXEC @Return = sp_executesql @SQL

  -- Create constraint
  SET @SQL = 'alter table '+@SchemaName+'.'+@TableName+' add constraint df_'+@TableName+'_'+@ColumnName+' default

'+@Definition+' for '+@ColumnName
  EXEC @Return = sp_executesql @SQL
  -- Get the next row
  FETCH NEXT FROM cursObjects
   INTO @TableName, @ColumnName, @DefaultConstraint, @SchemaName, @Definition
-- Close cursor
CLOSE cursObjects
DEALLOCATE cursObjects
-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
    SET @SQL = 'Error encountered in ['+ @SchemaName + '].[' + @Tablename + '].[' + @ColumnName + ']'
    RAISERROR(@SQL, 16, 1)

Saturday, December 13, 2008 - 2:39:40 AM - ivyflora Back To Top (2402)

Very nice article.. :):)

get free sql tips
agree to terms