[Solved]-Adding a column with a default value to an existing table in SQL Server

In this article, we will learn how to add a column with a default value to an existing table in SQL Server. I have specifically written this SQL Server article for newcomer developers and anyone who needs this query.

SQL Syntax:

ALTER TABLE {SQL_TABLENAME} 
ADD {TABLE_COLUMNNAME} {TYPE} {NULL|NOT NULL} 
CONSTRAINT {CONSTRAINT_NAME} DEFAULT {DEFAULT_VALUE}
WITH VALUES

SQL Example:

ALTER TABLE TABLENAME
        ADD COLNAME Bit NULL 
 CONSTRAINT D_TABLENAME_COLNAME 
    DEFAULT (0)--Optional Default-Constraint VALUE.
WITH VALUES --  Column Vlaue is Null then add Default Value for Existing Records.

Let’s take an example, I have created a table “Company_Users” with four-column Id, Name, Email, City. Now I want to add one more column ‘Country‘ with default value ‘India‘.

Sql Query:

ALTER TABLE Company_Users
        ADD Country nvarchar(200) NULL 
 CONSTRAINT D_Company_Users_Country
    DEFAULT ('India')--Optional Default-Constraint VALUE.
WITH VALUES --  Column Vlaue is Null then add Default Value for Existing Records.

Result:

Sql Table Create Script

CREATE TABLE [dbo].[Company_Users](
    [Id] [int] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](max) NOT NULL,
    [Email] [nvarchar](max) NOT NULL,
    [City] [nvarchar](max) NULL,
    [Country] [nvarchar](200) NULL,
 CONSTRAINT [PK_TblUsers] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)
) 
GO

If no value or constraint is given for a column in SQL, then by default, a NULL value is set. The DEFAULT constraint is used when someone wants to set a default value for the column. If no value is specified for the column, then the value set with the DEFAULT constraint is used.

For example, consider the 'Company_Users' table. If no value is provided for the 'Name' column in this table, then the default value is set to 'NO User NAME'.

CREATE TABLE Company_Employee(
Id INT NOT NULL,
UserName VARCHAR(100) DEFAULT ‘NO User NAME’
)

Adding a new column (COLNAME) of data type Bit to an existing table (TABLENAME). Let's break down each part of sql query to understand:

ALTER TABLE TABLENAME:  We want to make alterations to the structure of the table named TABLENAME.

ADD COLNAME Bit NULL:  We are adding a new column named COLNAME with a data type of Bit. The NULL keyword indicates that this column allows NULL values, meaning it can be left empty for existing rows.

CONSTRAINT D_TABLENAME_COLNAME DEFAULT (0): A default constraint for the new column. It gives a name to the constraint (D_TABLENAME_COLNAME) and sets the default value for the column to 0 when a new row is inserted and no value is provided for COLNAME.

--Optional Default-Constraint VALUE: This is a comment indicating that providing a default value is optional.

WITH VALUES: Eor existing rows where the COLNAME column is currently NULL, the default value specified in the constraint (0 in this case) should be assigned to those rows.

Above SQL code adds a new nullable column to the table named TABLENAME. The column is of type Bit (which typically stores boolean values, often represented as 0 for false and 1 for true). If a row is inserted without providing a value for this new column, it will default to 0. Additionally, for existing rows where this new column is NULL, the default value 0 will be assigned.

Now lets i want to delete the default constraint from the UserName column.
Source Code:

ALTER TABLE Company_Users
ALTER COLUMN UserName DROP DEFAULT;