solving sql server problems for millions of dbas and developers since 2006

SQL Product Highlight

Devart - dbForge SQL Complete

dbForge SQL Complete is a code autocomplete tool for SQL Server Management Studio and Visual Studio. Free and advanced paid editions of this useful add-in offer powerful autocompletion and formatting of T-SQL code that replaces native Microsoft T-SQL Intellisense.

Learn more!

Working with DEFAULT constraints in SQL Server

By: Armando Prato | Read Comments (5) | Print

Armando has over 24 years of industry experience and has been working with SQL Server since version 6.5.

Related Tips: More

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

Related Tips: More | Become a paid author

Last Update: 2/1/2008

Share:

Free SQL Server Learning

Comments and Feedback:

Saturday, December 13, 2008 - 2:39:40 AM - ivyflora	Read The Tip
Very nice article.. :):)

Saturday, June 06, 2009 - 3:43:53 PM - kanwarnasir

Read The Tip

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]
GO

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
DECLARE cursObjects CURSOR FAST_FORWARD
FOR
SELECT object_name(d.parent_object_id) TableName, col_name(parent_object_id, parent_column_id) ColumnName, d.name, 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
FETCH NEXT FROM cursObjects
INTO @TableName, @ColumnName, @DefaultConstraint, @SchemaName, @Definition

-- Set the return code to 0
SET @Return = 0

-- start a transaction
BEGIN TRAN

-- Cycle through the rows of the cursor to change default constraint name
WHILE ((@@FETCH_STATUS = 0) AND (@Return = 0))
BEGIN
  -- 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
END

-- Close cursor
CLOSE cursObjects
DEALLOCATE cursObjects

-- Check to see if the WHILE loop exited with an error.
IF (@Return = 0)
    COMMIT TRAN
ELSE
BEGIN
    ROLLBACK TRAN
    SET @SQL = 'Error encountered in ['+ @SchemaName + '].[' + @Tablename + '].[' + @ColumnName + ']'
    RAISERROR(@SQL, 16, 1)
END
GO

Saturday, March 06, 2010 - 5:00:33 PM - Hamburg Escort	Read The Tip
Thanks for sharing!

Monday, April 16, 2012 - 11:51:26 AM - Dhanasekar

Read The Tip

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

Monday, May 21, 2012 - 3:05:38 PM - Burak	Read The Tip
thank you for suggestions

Sponsor Information

"Amazing, Amazing, Amazing! SQL doctor is truly one of the most powerful tools I have seen."

Write, edit, and explore SQL effortlessly with SQL Prompt.

What grade do you think your SQL Servers get? Find out with a SQL Server Health Check consultant.

Get SQL Server Tips Straight from Kevin Kline.

Join the over million SQL Server Professionals who get their issues resolved daily.

Free Learning - Introduction to SQL Azure Delivered by Herve Roggero on Wednesday, June 13 @ 3:00 PM EST