How to set a default value for an existing column

Question

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?

Answer 1

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

Answer 2

cannot use alter column for that, use add instead

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

Answer 3

ALTER TABLE Employee ADD DEFAULT 'SANDNES' FOR CityBorn

Answer 4

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

Answer 5

This will work in SQL Server:

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

Answer 6

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;