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
This is awesome please provide some more informative posts. So we could improve our knowledge
Yes….:)