In this brief article, we will learn how to add a column with a default value to an existing table in the SQL Server.
SQL Server Default Constraint
As the name suggests, the default constraint defines a default value for a specific column. If we provide no value for the column, the SQL Server will use the specified value instead of null.
This is mainly useful when combined with other constraints, such as not null.
In most cases, you set the default constraint when creating a table. However, you can add a column with default value. Doing this affects the data that is inserted after the default constraint.
Add a Column With a Default Value
Assume we have a table as shown in the following query:
To add a column with a default value, we can use the alter table query as shown below:
If we insert new data, any missing value for the specified column will be replaced with the set default value:
The previous queries should return a result set as shown:
Notice only the new inserted record contains the default value for the specified column.
Add Column Default Value of All Inserts
To solve the problem, where all existing values are set to null, we need to use them with values clause as shown:
If we insert records with no values for the another_column provided, we should get the result as:
Notice that the existing columns are replaced with the default value.
In this article, we discovered how to add a column with a default value to an existing table in the SQL Server. Plus, we discussed the SQL server default constraint, adding a column with a default value, and adding a column with a default value of all inserts. We hope you found this article helpful. Check out the other Linux Hint articles for more SQL Server tutorials.