Modify Default value in SQL Server
sql, sql-server
Solution
When you add a column with default value, what happens is that there's a default constraint being created:
create table _temp
( x int default 1)
`sp_help _temp` result:
constraint_type constraint_name
DEFAULT on column x DF___temp__x__5A3B20F9
So default clause in column definition is just a shortcut for creating a constraint; default value is not an intrinsic property of a column. 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
Problem
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.