How To Add Column With Default Value In SQL

In this article, we will learn how to add a column with default value using the DEFAULT constraint in SQL.

To provide a default value for a column, the DEFAULT constraint is used.

If no value is specified, the default value will be added to all these records.

 

DEFAULT – CREATE TABLE:

Please check How To Create Table Using Query In SQL, to get a brief description of the CREATE TABLE statement.

Example

The subsequent statement would set a DEFAULT value for the “Author”, “Views”, and “CDate” columns when the “Article” table is created:

CREATE TABLE Article (
    ID int,
    Title varchar(100),
    Author varchar(100) DEFAULT 'Yasin',
    Views int DEFAULT 0,
    CDate date DEFAULT GETDATE()
);

 

DEFAULT – ALTER TABLE:

Please check How To Alter Table Using Query In SQL, to get a brief description of the ALTER TABLE statement.

• To ADD a DEFAULT Constraint

Syntax

ALTER TABLE TABLE_NAME 
ADD CONSTRAINT CONSTRAINT_NAME DEFAULT Value FOR columnName;

Example

The subsequent statement would add a DEFAULT constraint on the “Views” column using ALTER TABLE statement:

ALTER TABLE Article 
ADD CONSTRAINT DFArticle DEFAULT 0 FOR Views;

• To DROP a DEFAULT Constraint

Syntax

ALTER TABLE TABLE_NAME 
ALTER COLUMN columnName DROP DEFAULT;

Example

The subsequent statement would delete a DEFAULT constraint using ALTER TABLE statement:

ALTER TABLE Article 
ALTER COLUMN Views DROP DEFAULT;

 

Also, check How To Use CHECK Constraint In SQL