CODEWITHSUNDEEP
sqlsql-server
Ruben.Canton
Ruben.Canton

Reputation: 1303

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.

Upvotes: 60

Views: 127649

Answers (6)

AdamL
AdamL

Reputation: 13141

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

Upvotes: 89

user355308
user355308

Reputation: 569

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

Upvotes: 40

MrRimmer
MrRimmer

Reputation: 11

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.

Upvotes: 1

Igor
Igor

Reputation: 2369

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

Upvotes: 2

JodyT
JodyT

Reputation: 4412

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>

Upvotes: 7

Young Bob
Young Bob

Reputation: 743

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

Upvotes: 17

Related Questions