SQL DEFAULT

In this tutorial, we will learn how to use DEFAULT constraint using SQL.

SQL DEFAULT Constraint

SQL DEFAULT constraint is used to provide a default value for a column if no specific value is provided in the INSERT INTO statement.

SQL DEFAULT on CREATE TABLE Statement

CREATE TABLE Employee(
    ID int NOT NULL,
    EmpName varchar(255) NOT NULL,
    City varchar(255) DEFAULT 'Not Specified',
    Country int,
    Salary Decimal(18,2) DEFAULT '0.00'
);

SQL DEFAULT on ALTER TABLE Statement

To add a DEFAULT constraint on the column "City" when the table is already created, the statement is as follow:

SQL Server

ALTER TABLE Employee
ADD CONSTRAINT df_City 
DEFAULT 'Not Specified' FOR City;

MySQL

ALTER TABLE Employee
ALTER City SET DEFAULT 'Not Specified';

Oracle

ALTER TABLE Employee
MODIFY City DEFAULT 'Not Specified';

SQL DROP a DEFAULT Constraint

To DROP a DEFAULT constraint, a query is as follow:

SQL Server

To drop a constraint when you know the name of DEFAULT constraint, a query is as follow:

ALTER TABLE tableName
DROP CONSTRAINT constraintName;

ALTER TABLE Employee 
DROP CONSTRAINT df_City;

Oracle

ALTER TABLE Employee
ALTER COLUMN City DROP DEFAULT;

MySQL

ALTER TABLE Employee
ALTER City DROP DEFAULT;