Modify Default value in SQL Server

Question

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.

AdamL · Accepted Answer · 2020-05-13 11:20:37Z

89

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

edited May 13, 2020 at 11:20

answered Mar 21, 2013 at 12:20

AdamL

13.3k5 gold badges57 silver badges79 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Ruben.Canton Over a year ago

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.

marc_s Over a year ago

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)

AdamL Over a year ago

@Ruben.Canton: "sp_help TABLE_NAME" does it, too.

boubkhaled Over a year ago

exec sp_helpconstraint @objname = 'TableName'

Wrigglenite · Accepted Answer · 2019-07-16 09:44:11Z

40

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

edited Jul 16, 2019 at 9:44

Wrigglenite

1197 bronze badges

answered Apr 8, 2014 at 11:42

user355308

5694 silver badges10 bronze badges

1 Comment

MrBackend Over a year ago

@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;

Young Bob · Accepted Answer · 2013-03-21 12:19:48Z

17

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

answered Mar 21, 2013 at 12:19

Young Bob

7433 silver badges9 bronze badges

1 Comment

Ruben.Canton Over a year ago

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.

JodyT · Accepted Answer · 2013-03-21 12:20:14Z

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>

answered Mar 21, 2013 at 12:20

JodyT

4,4222 gold badges22 silver badges31 bronze badges

Comments

Igor · Accepted Answer · 2016-04-25 15:57:16Z

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

answered Apr 25, 2016 at 15:57

Igor

2,5681 gold badge20 silver badges20 bronze badges

Comments

MrRimmer · Accepted Answer · 2018-07-27 12:25:59Z

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.

Collectives™ on Stack Overflow

Modify Default value in SQL Server

6 Answers 6

4 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

4 Comments

1 Comment

1 Comment

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related