How to add a default value to an already existing column?

sql sql-server tsql alter-table default-constraint

52,251

Solution 1

Use:

ALTER TABLE dbo.mytable
ADD CONSTRAINT def_mycolumn DEFAULT GETDATE() FOR mycolumn

For more info, see: Working with Default Constraints

Solution 2

If you want to change the default value of an already existing column. You need to first drop the constraint and then add the constraint all over again as below

ALTER TABLE <TABLE> 
DROP CONSTRAINT <CONSTRAINT NAME>

ALTER TABLE <TABLE> 
ADD CONSTRAINT <CONSTRAINT NAME> DEFAULT <VALUE> for <COLUMN>

If you are not having the Constraint details of the table, you can use the below query

sp_helpconstraint <TABLE>

52,251

Author by

Earlz

Hello there! My name's Jordan Earls, but most people online know me as "earlz". I'm the lead developer and a co-founder of the Qtum project which brings the Ethereum Virtual Machine (ie, the thing that makes Solidity contracts function) to a UTXO based blockchain similar to Bitcoin. I've been programming since I was 13 and am completely self-taught. Low-level code like assembly and pointer arithmetic is the fun stuff for me. I also make music when I have time even though it's usually awful. Most of my personal projects are open source and BSD licensed. The majority of them are at bitbucket with the rest of them being listed on github Also, you can follow me on the twitters @earlzdotnet

Updated on May 05, 2020

Comments

Earlz about 4 years
I have an existing column in my SQL Server database. I have tried about everything I can think of but can not get a default value to be added to the column. What works in every other database is
```
alter table mytable 
  alter column mycolumn set default(now()) --mycolumn is a datetime
```
How do I do this in SQL Server?

The error I get for that exact syntax is incorrect syntax near the keyword 'set'
OMG Ponies about 14 years

@Earlz: Saw that, most appreciated :)