Problem
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?
Solution
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.
Create Table with a Constraint
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()
)
go
exec sp_helpconstraint [dbo.CustomerReport]
go 
Try to Alter Constraint on the Table
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() 
As you can see, we receive a syntax error.
Drop and Recreate the Constraint
To make the change, we need to DROP the constraint and then re-add it:
alter table dbo.CustomerReport
drop constraint df_CustomerReport_rundate
go
alter table dbo.CustomerReport
add constraint df_CustomerReport_rundate default getutcdate() for rundate
go
exec sp_helpconstraint [dbo.CustomerReport]
go
How to Find Constraint Name
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 and later: sys.default_constraints
The following example re-creates our CustomerReport table with SQL Server 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
go
create table dbo.CustomerReport
(
customer_id int,
report_id int,
rundate datetime not null default getdate()
)
go
exec sp_helpconstraint [dbo.CustomerReport]
go
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)
go
alter table dbo.CustomerReport
add constraint df_CustomerReport_rundate default getutcdate() for rundate
go
exec sp_helpconstraint [dbo.CustomerReport]
go
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'
go
Summary
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 but 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 COL_NAME() function in the SQL Server Books Online
