463

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?

gotqn
  • 42,737
  • 46
  • 157
  • 243
Nakul Chaudhary
  • 25,572
  • 15
  • 44
  • 47
  • 1
    What did you reading of MSDN ALTER TABLE say...? http://msdn.microsoft.com/en-us/library/ms187742(SQL.90).aspx – gbn Jul 22 '11 at 14:48
  • 2
    possible duplicate of [T-SQL command for adding a default constraint](http://stackoverflow.com/questions/4307075/t-sql-command-for-adding-a-default-constraint) –  Mar 11 '15 at 15:42
  • possible duplicate of [Add a column, with a default value, to an existing table in SQL Server](http://stackoverflow.com/questions/92082/add-a-column-with-a-default-value-to-an-existing-table-in-sql-server) – NoDataDumpNoContribution Apr 15 '15 at 20:55
  • 1
    That is mysql syntax. – Benjamin Goodacre Mar 20 '18 at 12:01
  • 1
    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 ? – joedotnot Jan 24 '20 at 21:41

14 Answers14

686

This will work in SQL Server:

ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;
Yuck
  • 49,664
  • 13
  • 105
  • 135
  • 77
    +1 for specifying a constraint name, so SQL Server doesn't create a random one. – HardCode Jul 22 '11 at 15:25
  • 48
    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. – Roman Starkov May 11 '13 at 12:06
  • 4
    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. – Jonny Piazzi Oct 27 '14 at 15:15
  • 4
    This only works if there is not an existing default constraint on the column. – pmcilreavy Aug 18 '16 at 21:20
  • 5
    @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... – Yuck Aug 19 '16 at 01:34
  • 4
    @Yuck. The answer would have been more useful if it included a check to see if a default already existed. – pmcilreavy Aug 21 '16 at 21:22
  • Is the `N` really supposed to be there, or is that a typo? `N'SANDNES'` – Yury Apr 29 '20 at 16:39
  • 1
    @Yury Not a typo: https://stackoverflow.com/questions/10025032/what-is-the-meaning-of-the-prefix-n-in-t-sql-statements-and-when-should-i-use-it – Yuck Apr 30 '20 at 12:42
  • @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. :) – eriegz Feb 24 '21 at 03:01
  • 1
    @eriegz 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. – Roman Starkov Feb 24 '21 at 09:46
  • @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... – eriegz Feb 24 '21 at 16:09
200
ALTER TABLE Employee ADD DEFAULT 'SANDNES' FOR CityBorn
hoodaticus
  • 3,772
  • 1
  • 18
  • 28
  • 4
    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). – Contango Apr 09 '14 at 11:49
  • 12
    @Contango 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](http://stackoverflow.com/questions/670946/dropping-unnamed-constraints/29370113#29370113) and you never have to name one again + can deal with all the other unnamed ones – JonnyRaa May 15 '15 at 13:31
  • 3
    @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. – Contango May 16 '15 at 17:35
  • 1
    (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 – Dave Feb 03 '16 at 10:04
  • 5
    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. – Triynko Dec 06 '16 at 03:24
74

cannot use alter column for that, use add instead

ALTER TABLE Employee 
ADD DEFAULT('SANDNES') FOR CityBorn
Carlos Quintanilla
  • 12,937
  • 3
  • 22
  • 25
40

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]
    
Michael
  • 8,362
  • 6
  • 61
  • 88
user3310402
  • 433
  • 4
  • 2
14

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
Dave
  • 3,093
  • 35
  • 32
9

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'
);
  1. 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'
  1. Drop existing default constraint for a column.

Syntax:

alter table 'table_name' drop constraint 'constraint_name'
  1. Add new default value constraint for that column:

Syntax:

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

cheers @!!!

Sunil Sharma
  • 1,297
  • 3
  • 17
  • 31
7

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]
Mise
  • 3,267
  • 1
  • 22
  • 22
6
ALTER TABLE [dbo].[Employee] ADD  DEFAULT ('N') FOR [CityBorn]
steave
  • 203
  • 1
  • 6
  • 9
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;
tibx
  • 840
  • 13
  • 20
6

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.

5

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 
David Fawzy
  • 1,056
  • 15
  • 17
  • Correct. `ADD DEFAULT` syntax allow you to add default value without establishing an explicit constrain (this was important in my case) – Mike Keskinov Jan 03 '23 at 13:49
2

Try following command;

ALTER TABLE Person11
ADD CONSTRAINT col_1_def
DEFAULT 'This is not NULL' FOR Address
Irshad
  • 3,071
  • 5
  • 30
  • 51
2
ALTER TABLE tblUser
 ADD CONSTRAINT DF_User_CreatedON DEFAULT GETDATE() FOR CreatedOn
Sheriff
  • 738
  • 10
  • 20
  • 1
    This is the same syntax already proposed a decade ago on [the accepted answer](https://stackoverflow.com/a/6791768/3025856)—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. – Jeremy Caney Jun 09 '21 at 19:06
1

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
ScottFoster1000
  • 577
  • 1
  • 10
  • 25