[sql-server] How to set a default value for an existing column

This isn't working in SQL Server 2008:

ALTER TABLE Employee ALTER COLUMN CityBorn SET DEFAULT 'SANDNES'

The error is:

Incorrect syntax near the keyword 'SET'.

What am I doing wrong?

Just Found 3 simple steps to alter already existing column that was null before

update   orders
set BasicHours=0 where BasicHours is null

alter table orders 
add default(0) for BasicHours

alter table orders 
alter  column CleanBasicHours decimal(7,2) not null 

ALTER TABLE Employee ADD DEFAULT 'SANDNES' FOR CityBorn

cannot use alter column for that, use add instead

ALTER TABLE Employee 
ADD DEFAULT('SANDNES') FOR CityBorn

There are two scenarios where default value for a column could be changed,

1. At the time of creating table
2. Modify existing column for a existing table.

1. At the time of creating table / creating new column.

Query

create table table_name
(
    column_name datatype default 'any default value'
);

2. Modify existing column for a existing table

In this case my SQL server does not allow to modify existing default constraint value. So to change the default value we need to delete the existing system generated or user generated default constraint. And after that default value can be set for a particular column.

Follow some steps :

1. List all existing default value constraints for columns.

Execute this system database procedure, it takes table name as a parameter. It returns list of all constrains for all columns within table.

execute [dbo].[sp_helpconstraint] 'table_name'

2. Drop existing default constraint for a column.

Syntax:

alter table 'table_name' drop constraint 'constraint_name'

3. Add new default value constraint for that column:

Syntax:

alter table 'table_name' add default 'default_value' for 'column_name'

cheers @!!!

You can use following syntax, For more information see this question and answers : Add a column with a default value to an existing table in SQL Server

Syntax :

ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

Example :

ALTER TABLE SomeTable
ADD SomeCol Bit NULL --Or NOT NULL.
CONSTRAINT D_SomeTable_SomeCol --When Omitted a Default-Constraint Name is 
autogenerated.
DEFAULT (0)--Optional Default-Constraint.
WITH VALUES --Add if Column is Nullable and you want the Default Value for Existing Records.

Another way :

Right click on the table and click on Design,then click on column that you want to set default value.

Then in bottom of page add a default value or binding : something like '1' for string or 1 for int.

Like Yuck's answer with a check to allow the script to be ran more than once without error. (less code/custom strings than using information_schema.columns)

IF object_id('DF_SomeName', 'D') IS NULL BEGIN
    Print 'Creating Constraint DF_SomeName'
   ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;
END

First drop constraints

https://stackoverflow.com/a/49393045/2547164

DECLARE @ConstraintName nvarchar(200)
SELECT @ConstraintName = Name FROM SYS.DEFAULT_CONSTRAINTS
WHERE PARENT_OBJECT_ID = OBJECT_ID('__TableName__')
AND PARENT_COLUMN_ID = (SELECT column_id FROM sys.columns
                        WHERE NAME = N'__ColumnName__'
                        AND object_id = OBJECT_ID(N'__TableName__'))
IF @ConstraintName IS NOT NULL
EXEC('ALTER TABLE __TableName__ DROP CONSTRAINT ' + @ConstraintName)

Second create default value

ALTER TABLE [table name] ADD DEFAULT [default value] FOR [column name]

Hoodaticus's solution was perfect, thank you, but I also needed it to be re-runnable and found this way to check if it had been done...

IF EXISTS(SELECT * FROM information_schema.columns 
           WHERE table_name='myTable' AND column_name='myColumn' 
             AND Table_schema='myDBO' AND column_default IS NULL) 
BEGIN 
  ALTER TABLE [myDBO].[myTable] ADD DEFAULT 0 FOR [myColumn] --Hoodaticus
END

Try following command;

ALTER TABLE Person11
ADD CONSTRAINT col_1_def
DEFAULT 'This is not NULL' FOR Address

in case a restriction already exists with its default name:

-- Drop existing default constraint on Employee.CityBorn
DECLARE @default_name varchar(256);
SELECT @default_name = [name] FROM sys.default_constraints WHERE parent_object_id=OBJECT_ID('Employee') AND COL_NAME(parent_object_id, parent_column_id)='CityBorn';
EXEC('ALTER TABLE Employee DROP CONSTRAINT ' + @default_name);

-- Add default constraint on Employee.CityBorn
ALTER TABLE Employee ADD CONSTRAINT df_employee_1 DEFAULT 'SANDNES' FOR CityBorn;

The correct way to do this is as follows:

1. Run the command:

   sp_help [table name] 
   

2. Copy the name of the CONSTRAINT.

3. Drop the DEFAULT CONSTRAINT:

   ALTER TABLE [table name] DROP [NAME OF CONSTRAINT] 
   

4. Run the command below:

   ALTER TABLE [table name] ADD DEFAULT [DEFAULT VALUE] FOR [NAME OF COLUMN]
   

ALTER TABLE [dbo].[Employee] ADD  DEFAULT ('N') FOR [CityBorn]