Learn more about SQL Server tools

mssqltips logo
 

Tutorials          DBA          Dev          BI          Career          Categories          Webcasts          Scripts          Today's Tip          Join

Tutorials      DBA      Dev      BI      Categories      Webcasts

DBA    Dev    BI    Categories

 

Working with DEFAULT constraints in SQL Server


By:   |   Updated: 2008-02-01   |   Comments (9)   |   Related: More > 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


Last Updated: 2008-02-01


get scripts

next tip button



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.

View all my tips




Post a comment or let the author know this tip helped.

All comments are reviewed, so stay on subject or we may delete your comment. Note: your email address is not published. Required fields are marked with an asterisk (*).

*Name    *Email    Email me updates 


Signup for our newsletter
 I agree by submitting my data to receive communications, account updates and/or special offers about SQL Server from MSSQLTips and/or its Sponsors. I have read the privacy statement and understand I may unsubscribe at any time.



    



Tuesday, February 05, 2013 - 7:28:18 AM - Hiren Khirsaria Back To Top

Very Useful Article...!!!!

Thanks


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

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


Monday, August 06, 2012 - 9:22:57 AM - Armando Prato Back To Top

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


Monday, August 06, 2012 - 7:12:28 AM - Manoj Kumar Sharma Back To Top

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

thank you for suggestions


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

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 06, 2010 - 5:00:33 PM - Hamburg Escort Back To Top
Thanks for sharing!

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

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 '[email protected]+'.'[email protected]+' drop constraint ' + @DefaultConstraint
  EXEC @Return = sp_executesql @SQL

  -- Create constraint
  SET @SQL = 'alter table '[email protected]+'.'[email protected]+' add constraint df_'[email protected]+'_'[email protected]+' default

'[email protected]+' for '[email protected]
  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, December 13, 2008 - 2:39:40 AM - ivyflora Back To Top

Very nice article.. :):)


Learn more about SQL Server tools