Add column with default value to existing table in sql server

There are some sudden requirement where we want to add column to an existing table with some default constraint.  A default constraint is used to assign some value to the column so whenever the records are inserted into the table and the column with default constraint is not there, it will automatically assign default value to the column.

The basic syntax to add default constraint to an existing table is as below.

ALTER  TABLE  {Table_Name}
ADD  {Column_Name}  {Column_Data_Tyle}  {NULL/NOT NULL}
CONSTRAINT  {Column_Constraint_Name}  DEFAULT  {Default_Column_Value}

Lets go through the simple example to better understand the above syntax.

Suppose we have a employee table with below configuration and the table is already in use with some data in it.

EmployeeId First Name Last Name Address City State
1 Justin Stevens 100 Main Street Adger Alabama
2 Tom Hanson 56 Wall Street Bremen Alabama
3 TAYLOR Swif 111 Second Street Lafayette Colorado
4 Tom WALKER 1212 Suite Bremen Colorado

So lets add a country column with default value as a “USA”.

ALTER TABLE Employee
ADD Country  VARCHAR(20) NOT NULL
CONSTRAINT Emp_Country_Constraint DEFAULT ‘USA’

After executing above script the out put of the table will be some thing like below.

EmployeeId First Name Last Name Address City State Country
1 Justin Stevens 100 Main Street Adger Alabama USA
2 Tom Hanson 56 Wall Street Bremen Alabama USA
3 TAYLOR Swif 111 Second Street Lafayette Colorado USA
4 Tom WALKER 1212 Suite Bremen Colorado USA

Now try to add some new row without defining country column.

INSERT INTO [TechnoThirsty].[dbo].[Employee]
([FirstName],[LastName],[Address],[City],[State])
VALUES
(‘John’,’Smith’,’123 Test Street’,’Rowlett’,’Texas’)

And the out put table is something like below.

EmployeeId First Name Last Name Address City State Country
1 Justin Stevens 100 Main Street Adger Alabama USA
2 Tom Hanson 56 Wall Street Bremen Alabama USA
3 TAYLOR Swif 111 Second Street Lafayette Colorado USA
4 Tom WALKER 1212 Suite Bremen Colorado USA
5 John Smith 123 Test Street Rowlett Texas USA

So If we do not specify column value with default constraint, it will automatically assign default value to the column.

Here was the full detailed description of how to add column with default value to existing table in sql server. Please give us your valuable feedbacks to improve our blog/posts of TechnoThirsty.com

2 comments

Leave a Reply