By: Armando Prato | Comments (10) | Related: > Constraints
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.
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
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. 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
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 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
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
About the author
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
