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?

Vocal

What did you reading of MSDN ALTER TABLE say...? msdn.microsoft.com/en-us/library/ms187742(SQL.90).aspx – Heterosexuality

possible duplicate of T-SQL command for adding a default constraint – Resolvent

possible duplicate of Add a column, with a default value, to an existing table in SQL Server – Akela

That is mysql syntax. – Inoperable

why can't everyone just stick to a standard ? (not a real question, everyone referring to microsoft and mysql or other vendors). is there even an ansi/iso standard way of doing this ? – Murton

Answer 1

This will work in SQL Server:

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

Ange

+1 for specifying a constraint name, so SQL Server doesn't create a random one. – Hedrick

MSSQL is weird for calling default values "constraints". If anything, they are relaxations; the opposite of a constraint! They make more things valid, not fewer. – Quirk

It's not weird if you think in a alternative way like: The default constraint prevent MSSQL to throw a error if you not specify the column. So it's not a constraint to user, but is a constraint to MSSQL. – Micropathology

This only works if there is not an existing default constraint on the column. – Manslaughter

@fallenidol This only works if there is not an existing default constraint on the column. Right, because by definition you can't have more than one default value... – Ange

@Yuck. The answer would have been more useful if it included a check to see if a default already existed. – Manslaughter

Is the N really supposed to be there, or is that a typo? N'SANDNES' – Uam

@Uam Not a typo: #10025532 – Ange

@RomanStarkov Hmmm, I don't see it that way. With a default value, the range of possible values is reduced (i.e.: can't be NULL), which is exactly the function of a constraint. :) – Hedelman

@Hedelman not true, the default constraint does not prevent you from setting the column to NULL. You can use the constraint with a nullable type. Literally the only thing it does is to allow you to omit the value completely when inserting. – Quirk

@RomanStarkov Right. I didn't mean that it was literally impossible to make that column NULL thereafter (in fact, you could even have nulls in the column before adding the default value constraint). But you raise a good point about how default values aren't really constraints. Now you got me wondering where I would store default values if I were designing my own relational DMBS... – Hedelman

Answer 2

ALTER TABLE Employee ADD DEFAULT 'SANDNES' FOR CityBorn

Fouquet

This works in MSSQL, but it has the disadvantage that it doesn't name the constraint that it creates behind the scenes (see the higher voted answer above). – Upset

@Upset I think its better to not bother naming every single constraint on the offchance you need to modify one of them. Just write a procedure for dropping automatically named constraints and you never have to name one again + can deal with all the other unnamed ones – Hoatzin

@Jonny Leeds. Good point. I like to also name the constraints, as it's good documentation for anyone else trying to modify (or simply understand) the database schema. This is less important if one is a solo operator working in a team of one. – Upset

(EESH! Sorry about the lack of line feeds - pasted below for clarity!) I needed it to be re-runnable and found this way to check if it had been done already... 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] END – Carlyle

If there's only one default constraint... why do you have to name it? Looks like other database engines provide syntax for adding, updating, removing the default value for a column without any name other than the column name, which makes sense. The above code fails, btw, if a default already exists. It's ridiculous that SQL Server requires a complex join against a system table just to identify the name of the default constraint, which shouldn't be necessary because there can be only one default constraint on a column. – Hardej

Answer 3

cannot use alter column for that, use add instead

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

Polypary

don't know why this answer doesn't get upvotes. It's exactly the same as the one before and was answered earlier... – Hurwit

Answer 4

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]
   

Semasiology

StackOverflow.com is English language only. The Portuguese site is pt.stackoverflow.com – Bag

This does not add nothing new to other answers, is a manual method - cannot be scripted, and it states as the "the correct way".. – Southeastwards

Answer 5

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

Carlyle

Answer 6

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

Biconcave

Answer 7

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;

Frosty

This was very helpful for scripting since the constraint was auto-generated and had a different name in different environments. – Statist

Answer 8

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.

Lyublin

Answer 9

First drop constraints

https://mcmap.net/q/81261/-how-do-you-drop-a-default-value-from-a-column-in-a-table

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]

Madly

Answer 10

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

Tarim

Answer 11

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 

Lindane

Correct. ADD DEFAULT syntax allow you to add default value without establishing an explicit constrain (this was important in my case) – Outright

Answer 12

Try following command;

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

Vetter

Answer 13

ALTER TABLE tblUser
 ADD CONSTRAINT DF_User_CreatedON DEFAULT GETDATE() FOR CreatedOn

Gliadin

This is the same syntax already proposed a decade ago on the accepted answer—but, oddly, chooses not to use the identifiers or data types explicitly established by the author in the original question, thus making it less relevant. – Mame

Answer 14

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

Cassaba