Follow @endyourif rss Facebook LinkedIn

Add a column with a default value to an existing table in SQL Server Add a column with a default value to an existing table in SQL Server

Published on Jan 29, 2020

For any given project that has been existence for any amount of time, at some point your table design will change and you will need to add a new column to your SQL table. More specifically, this value should be NOT NULL so you want/need to set a default value when adding the new column.



Adding default constraint with SQL



To add a new column, you need to perform an ALTER TABLE ADD COLUMN to create the new column:


ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 


This will create a new column with the name you give it. To add a default constraint, you need to extend the command to include the following:


ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
DEFAULT {default_value}


Now my DBA doesn't like this because it creates a unique name with some gobbly gook at the end, e.g. DF__MyTable__MyColumn__6FC7AEG8. This of course is random each time you run it, which makes it more difficult to delete it because it won't be consistent on different databases that you may run this script.

To add with column with a default constraint and a specific name you can run:


ALTER TABLE {TABLENAME} 
ADD {COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}

Tags: SQL | mysql | mssql

My Books
ASP.NET MVC 5 With Bootstrap and Knockout.js
Knockout.js Building Dynamic Client-Side Applications
20 Recipes for Programming MVC 3
20 Recipes for Programming PhoneGap
Rapid Application Development with CakePHP