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
