Modify Default Value in SQL Server

Modify Default value in SQL Server

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

How to set a default value for an existing column

This will work in SQL Server:

ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;

Alter the column default value for an existing column in SQL Server 2012

You need to drop and re-add the default constraint to specify a different value.

ALTER TABLE dbo.TRANSACTIONS DROP CONSTRAINT DF_TRANSACTIONS_ROLE;
ALTER TABLE dbo.TRANSACTIONS ADD CONSTRAINT DF_TRANSACTIONS_ROLE DEFAULT ('S') FOR [ROLE];

However, since you didn't explicitly name the constraint (which is a best practice), you'll need to determine the existing constraint name for the DROP statement:

SELECT name
FROM sys.default_constraints AS dc
WHERE
    dc.parent_object_id = OBJECT_ID(N'dbo.TRANSACTIONS')
    AND dc.parent_column_id = COLUMNPROPERTYEX(dc.parent_object_id, 'ROLE', 'ColumnID');

How to change the default value of a column in SQL Server

In SQL Server, defaults are defined as constraints associated with a specific column in a table. All constraints are assigned a name; this is important, because once the constraint is created, if you want to modify it you have to reference it by this name. (And I’ll be watching this question, to see if I’m wrong.)

Based on this sample table:

CREATE TABLE MyTable
 (
   MyTableId  int          not null
  ,SomeData   varchar(50)  not null  default 'Foo'
  ,MoreData   datetime     not null  default CURRENT_TIMESTAMP
 )

Step 1: Determine if a constraint exists on a column. Several ways to do this, all involving system views and/or metadata functions. Here’s a quick one, where ‘MyTable’ and ‘SomeData’ could be set as parameters:

SELECT name
 from sys.default_constraints
 where parent_object_id = object_id('MyTable')
  and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId')

Try it, and you’ll see that the name generated is essentially random blather. To determine if a default exists on a column, you could do:

IF exists (select name
            from sys.default_constraints
            where parent_object_id = object_id('MyTable')
             and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId'))
    PRINT 'Default found'
ELSE
    PRINT 'NoDefault'

To drop an existing default where you don’t know the name, you’ll have to build dynamic SQL. (that code in the referenced article is wrong, and clearly never tested.) @Călin does it slightly differently than I’d do it, but the idea’s the same:

DECLARE @Command nvarchar(max)

SELECT @Command = 'ALTER TABLE MyTable drop constraint ' + name
 from sys.default_constraints
 where parent_object_id = object_id('MyTable')
  and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId')

IF @Command is not null
    EXECUTE sp_executeSQL @Command

(With error checking, parameters for the table and and column being checked, and so on.)

Lastly, you can avoid most of this by naming the defaults when you create the constraint, like so:

CREATE TABLE MyTable
 (
   MyTableId  int          not null
  ,SomeData   varchar(50)  not null
    constraint DF_MyTable__SomeData  default 'Foo'
  ,MoreData   datetime     not null
    constraint DF_MyTable__MoreData  default CURRENT_TIMESTAMP
 )

Or like so:

IF not exists (select name
                from sys.default_constraints
                where parent_object_id = object_id('MyTable')
                 and parent_column_id = columnproperty(object_id('MyTable'), 'SomeData', 'ColumnId'))
    ALTER TABLE MyTable
     add constraint DF_MyTable__SomeData
      default 'Foo' for SomeData

altering DEFAULT constraint on column SQL Server

When you add a default, you should use names for your constraints. This way you can later refer to those constraints by name.

ALTER TABLE [dbo].[PMIPatients] ADD CONSTRAINT [PatientFallRiskLevel_Default] DEFAULT ((0)) FOR PatientFallRiskLevel

Then you can drop it using:

ALTER TABLE [dbo].[PMIPatients] DROP CONSTRAINT [PatientFallRiskLevel_Default] 

Alter default value of column in mssql

Alter table does not have an option to supply a default constraint so you have to issue two commands; your first to alter the column, then you can create its default constraint:

alter table [Table] add constraint DF_ColumnName default 'y' for [column];

Alter table to modify default value of column

Your belief about what will happen is not correct. Setting a default value for a column will not affect the existing data in the table.

I create a table with a column col2 that has no default value

SQL> create table foo(
  2    col1 number primary key,
  3    col2 varchar2(10)
  4  );

Table created.

SQL> insert into foo( col1 ) values (1);

1 row created.

SQL> insert into foo( col1 ) values (2);

1 row created.

SQL> insert into foo( col1 ) values (3);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

If I then alter the table to set a default value, nothing about the existing rows will change

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'foo' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3

SQL> insert into foo( col1 ) values (4);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

Even if I subsequently change the default again, there will still be no change to the existing rows

SQL> alter table foo
  2    modify( col2 varchar2(10) default 'bar' );

Table altered.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo

SQL> insert into foo( col1 ) values (5);

1 row created.

SQL> select * from foo;

      COL1 COL2
---------- ----------
         1
         2
         3
         4 foo
         5 bar

Alter column default value

I think issue here is with the confusion between Create Table and Alter Table commands.
If we look at Create table then we can add a default value and default constraint at same time as:

<column_definition> ::= 
column_name <data_type>
    [ FILESTREAM ]
    [ COLLATE collation_name ] 
    [ SPARSE ]
    [ NULL | NOT NULL ]
    [ 
        [ CONSTRAINT constraint_name ] DEFAULT constant_expression ] 
      | [ IDENTITY [ ( seed,increment ) ] [ NOT FOR REPLICATION ] 
    ]
    [ ROWGUIDCOL ]
    [ <column_constraint> [ ...n ] ] 
    [ <column_index> ]
 ex: 
CREATE TABLE dbo.Employee 
(
     CreateDate datetime NOT NULL 
     CONSTRAINT DF_Constraint DEFAULT (getdate())
) 
ON PRIMARY;

you can check for complete definition here:
http://msdn.microsoft.com/en-IN/library/ms174979.aspx

but if we look at the Alter Table definition then with ALTER TABLE ALTER COLUMN you cannot add
CONSTRAINT the options available for ADD are:

 | ADD 
    { 
        <column_definition>
      | <computed_column_definition>
      | <table_constraint> 
      | <column_set_definition> 
    } [ ,...n ]

Check here: http://msdn.microsoft.com/en-in/library/ms190273.aspx

So you will have to write two different statements one for Altering column as:

ALTER TABLE MyTable ALTER COLUMN CreateDate DATETIME NOT NULL;

and another for altering table and add a default constraint

ALTER TABLE MyTable ADD CONSTRAINT DF_Constraint DEFAULT GetDate() FOR CreateDate;

Hope this helps!!!

---

Related Topics