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

User · Answer

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

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

At the time of creating table / creating new column.

Query

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

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 :

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'

Drop existing default constraint for a column.

Syntax:

alter table 'table_name' drop constraint 'constraint_name'

Add new default value constraint for that column:

Syntax:

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

cheers @!!!

User · Answer

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

User · Answer

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

User · Answer

cannot use alter column for that  use add instead  ALTER TABLE Employee  ADD DEFAULT  SANDNES   FOR CityBorn

User · Answer

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

User · Answer

ALTER TABLE  dbo   Employee  ADD  DEFAULT   N   FOR  CityBorn

User · Answer

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

User · Answer

Try following command   ALTER TABLE Person11 ADD CONSTRAINT col 1 def DEFAULT  This is not NULL  FOR Address

User · Answer

This will work in SQL Server   ALTER TABLE Employee ADD CONSTRAINT DF SomeName DEFAULT N SANDNES  FOR CityBorn

User · Answer

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

User · Answer

ALTER TABLE Employee ADD DEFAULT  SANDNES  FOR CityBorn

User · Answer

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

User · Answer

The correct way to do this is as follows     Run the command    sp help  table name    Copy the name of the CONSTRAINT  Drop the DEFAULT CONSTRAINT   ALTER TABLE  table name  DROP  NAME OF CONSTRAINT    Run the command below   ALTER TABLE  table name  ADD DEFAULT  DEFAULT VALUE  FOR  NAME OF COLUMN

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

Examples related to sql-server

Examples related to sql-server-2008

Examples related to tsql

Examples related to default-value