Tuesday, October 30, 2007

Changing default column values - SQL Server

A very common mistake in T-SQL is trying to alter column definition trying to alter DEFAULT value (constraint) for that column. Probably, developers expect to have this because it is possible to assign default value for a column during table definition:
CREATE TABLE Table1 (
col1 INT NOT NULL DEFAULT (0),
col2 INT NOT NULL
)
Due to possibility to change column type from int to varchar(20) or null-ability with
ALTER TABLE Table1 ALTER COLUMN col2 VARCHAR(20) NULL
a lot of developers expect to be able to change a column default value with
ALTER TABLE Table1 ALTER COLUMN col1 DEFAULT (1)
Error message is displayed trying to execute this: "Incorrect syntax near the keyword 'DEFAULT'".
Mistake.
With a create table syntax mentioned earlier,
SQL Server creates default constraint with auto generated name like "DF__Table1__col1__57DD0BE4" and stores default value in [text] column of syscomments table.
So, to change a default value for a column you should change a constraint definition and the only way to do that is to drop current constraint and create the new one:
ALTER TABLE table1 DROP CONSTRAINT
DF__Table1__col1__57DD0BE4
ALTER TABLE table1 WITH NOCHECK ADD CONSTRAINT [Df_test_col1] DEFAULT (1) FOR col1


Compare databases with the fastest tool - DBTYP.NET

3 comments:

JohnOpincar said...

Exactly what I was looking for. Thanks!

granadaCoder said...

You can make this a little cleaner by naming the default constraint at the beginning, to avoid the MS random name.

The below will add a column, non null, add a temporary default, then drop the default.



if exists (select * from sysobjects where id = object_id('Person'))
BEGIN
if not exists ((SELECT * FROM sysobjects O INNER JOIN syscolumns C ON O.ID=C.ID WHERE ObjectProperty(O.ID,'IsUserTable')=1
AND O.Name='Person'
AND C.Name='FavoriteColor') )
BEGIN
ALTER TABLE dbo.Person
ADD
FavoriteColor varchar(24) not null CONSTRAINT [DF_Person_FavoriteColor_Temporary] default 'red'
END

if exists (select * from sysobjects where name like 'DF_Person_FavoriteColor_Temporary' and xtype = 'D')
begin
ALTER TABLE dbo.Person DROP CONSTRAINT DF_Person_FavoriteColor_Temporary
end


END


Ash said...


I recently had to change the default values for lots of columns. I streamlined the process a bit. Check it out.