57

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.

Ruben.Canton
  • 1,263
  • 3
  • 15
  • 29

6 Answers6

86

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
AdamL
  • 12,421
  • 5
  • 50
  • 74
  • 17
    I would only add how to get the constraint names to your explanation: SELECT NAME FROM sys.default_constraints WHERE parent_object_id = OBJECT_ID('dbo.MyTable'); Just doing that will help you knowing which constraint to remove/add again. – Ruben.Canton Mar 21 '13 at 12:31
  • 6
    This clearly shows why it's so important to **explicitly name** your constraints! Imagine you want to drop this constraint .... how much easier is it if you know it's called `DF_Temp_X` (instead of the system-generated `DF___temp__x__5A3B20F9`) – marc_s Mar 21 '13 at 12:36
  • 3
    @Ruben.Canton: "sp_help TABLE_NAME" does it, too. – AdamL Mar 21 '13 at 12:36
  • 1
    exec sp_helpconstraint @objname = 'TableName' – boubkhaled Jan 14 '19 at 15:05
37
DECLARE @Command nvarchar(max), @ConstraintName nvarchar(max), @TableName nvarchar(max), @ColumnName nvarchar(max)
SET @TableName = 'TableName'
SET @ColumnName = 'ColumnName'
SELECT @ConstraintName = name
    FROM sys.default_constraints
    WHERE parent_object_id = object_id(@TableName)
        AND parent_column_id = columnproperty(object_id(@TableName), @ColumnName, 'ColumnId')

SELECT @Command = 'ALTER TABLE ' + @TableName + ' DROP CONSTRAINT ' + @ConstraintName  
EXECUTE sp_executeSQL @Command

SELECT @Command = 'ALTER TABLE ' + @TableName + ' ADD CONSTRAINT ' + @ConstraintName + ' DEFAULT 1 FOR ' + @ColumnName 
EXECUTE sp_executeSQL @Command
Wrigglenite
  • 119
  • 7
user355308
  • 539
  • 4
  • 10
  • 3
    @LukasEder Probably as a reaction to the amount of code SQL Server requires to drop a default value when the constraint name is system generated, as compared to e.g. PostgreSQL: ALTER TABLE TableName ALTER COLUMN ColumnName DROP DEFAULT; – MrBackend Jul 02 '15 at 12:31
16

You should drop the DEFAULT constraint and add a new one like this

alter table Mytable
drop constraint <constraint name>
go
alter table MyTable
add constraint df_MyTable_MyColumn default -1 for MyColumn
go

Use sp_helpconstraint MyTable to get constraint names

Young Bob
  • 733
  • 3
  • 9
  • I get it now, the other pages where also talking about the constraint but I didn't read in detail and looking at the statements I thought it was answering something different. Thanks for the clarification. – Ruben.Canton Mar 21 '13 at 12:22
7

DEFAULT is a constraint. You want to use ALTER TABLE ADD CONSTRAINT.

ALTER TABLE MyTable
ADD CONSTRAINT <constraint name>
DEFAULT -1 FOR MyColumn

You'll need to drop the current constraint first, like:

ALTER TABLE MyTable
DROP CONSTRAINT <constraint name>
JodyT
  • 4,324
  • 2
  • 19
  • 31
2

Add default value for column if it is not exists

DECLARE @defaultLock VARCHAR(100)
SELECT @defaultLock = (SELECT object_definition(default_object_id) AS definition FROM sys.columns WHERE name = 'Lock' AND object_id = object_id('dbo.Positions'))
    IF @defaultLock IS NULL
        ALTER TABLE Positions ADD DEFAULT (0) FOR Lock
Igor
  • 1,589
  • 15
  • 15
1

I've had to change the default constraint on many fields in existing databases so wanted do do it in one hit. Hope this helps...

select 'USE ' + DB_NAME()
SELECT 'ALTER TABLE '+ src3.name + '.' + src1.name +' drop constraint '+ src.name + '
ALTER TABLE ' + src3.name + '.' +  src1.name +' ADD CONSTRAINT '+ src.name + ' DEFAULT getutcdate() FOR ' + src2.name -- amend default constrint accordingly.
    FROM sys.tables src1
      inner join sys.schemas src3
        on src3.schema_id = src1.schema_id
      inner join sys.columns src2
        on src2.object_id = src1.object_id
      inner join sys.default_constraints src
        on src.parent_column_id = src2.column_id
           and src.parent_object_id = src1.object_id
    WHERE  (-- pick up existing constraint definition
            src.definition like '%getdate%'
             or src.definition like '%current\_timestamp%' escape '\'
            )
            and src1.type = 'U'
order by  src3.name + '.' + src1.name

Obviously you'll need to amend the query and copy the output to another tab to be executed.

MrRimmer
  • 11
  • 2