In this article, we will learn, How to Add a column with a default value to an existing table in SQL Server.I have specially written this sql server article for newcomers developer and anyone need 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 User Table

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 on the column of SQL then by default NULL value is set.The DEFAULT constraint is used if anyone wants to set the default value to the column. If no value is given on the column then the value set with DEFAULT constraint.
Example for ‘DEFAULT’ Constraint in SQL

For example, the ‘Company_Users’ table. If no value is given in the Name 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’
)

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

ALTER TABLE Company_Users
ALTER COLUMN UserName DROP DEFAULT;

The post [Solved]-Adding a column with a default value to an existing table in SQL Server appeared first on Software Development | Programming Tutorials.



Read More Articles