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.
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
ALTER TABLE TABLE_NAME ADD CONSTRAINT CONSTRAINT_NAME DEFAULT Value FOR columnName;
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
ALTER TABLE TABLE_NAME ALTER COLUMN columnName DROP DEFAULT;
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