479

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?

Improve this question
5

14 Answers 14

Reset to default
709

This will work in SQL Server:

ALTER TABLE Employee ADD CONSTRAINT DF_SomeName DEFAULT N'SANDNES' FOR CityBorn;
Improve this answer
Sign up to request clarification or add additional context in comments.

11 Comments

+1 for specifying a constraint name, so SQL Server doesn't create a random one.
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.
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.
This only works if there is not an existing default constraint on the column.
@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...
|
212 
ALTER TABLE Employee ADD DEFAULT 'SANDNES' FOR CityBorn
Improve this answer

5 Comments

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 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
@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.
(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
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.
78

cannot use alter column for that, use add instead

ALTER TABLE Employee ADD DEFAULT('SANDNES') FOR CityBorn
Improve this answer

1 Comment

don't know why this answer doesn't get upvotes. It's exactly the same as the one before and was answered earlier...
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]
Improve this answer

2 Comments

StackOverflow.com is English language only. The Portuguese site is pt.stackoverflow.com
This does not add nothing new to other answers, is a manual method - cannot be scripted, and it states as the "the correct way"..
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
Improve this answer

Comments

11

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;
Improve this answer

1 Comment

This was very helpful for scripting since the constraint was auto-generated and had a different name in different environments.
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 @!!!

Improve this answer

Comments

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.

Improve this answer

Comments

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]
Improve this answer

Comments

6 
ALTER TABLE [dbo].[Employee] ADD DEFAULT ('N') FOR [CityBorn]
Improve this answer

Comments

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
Improve this answer

1 Comment

Correct. ADD DEFAULT syntax allow you to add default value without establishing an explicit constrain (this was important in my case)
3 
ALTER TABLE tblUser ADD CONSTRAINT DF_User_CreatedON DEFAULT GETDATE() FOR CreatedOn
Improve this answer

1 Comment

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.
2

Try following command;

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

Comments

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
Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.