Ruben.Canton Ruben.Canton - 7 months ago 30
SQL Question

Modify Default value in SQL Server

I'm trying to change the default value of a column using a SQL statement in SQL Server 2008. I've found in many places how to set the default value when you create a table/add a column but not how to set it/modify it once the column already exists.

This is what I can use to set it on adding:

ALTER TABLE MyTable ADD MyColumn int NOT NULL DEFAULT 0


And that works, but if I try to modify it later:

ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL DEFAULT -1
ALTER TABLE MyTable ALTER COLUMN MyColumn int NOT NULL SET DEFAULT -1


None of those are syntactically correct, and I don't find the syntax to do what I pretend anywhere. The only option I come with is to add a new column, copy values from previous column, then remove previous column and new column to make the change, but that doesn't seem right to me.

Is there a way of doing what I want in just one simple sentence?

Thanks.

Answer

When you add a column with default value, what happens is that there's a default constraint being added:

create table _temp 
( x int default 1)

sp_help result:

constraint_type constraint_name
DEFAULT on column x DF___temp__x__5A3B20F9

So this clause is a shortcut for creating constraint. If you want to modify default for already existing column, you have to drop the constraint first:

alter table _temp drop constraint DF___temp__x__5A3B20F9 

And then create a new default constraint:

alter table _temp add constraint DF_temp_x default 2 for x