SQL - DEFAULT

The SQL DEFAULT constraint is used to set the default value for a column. The column takes default value when a new record is inserted without specifying any value.

SQL DEFAULT constraint with CREATE TABLE statement

The below mentioned SQL code creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which DEFAULT constraint is applied on column City.

MySQL / SQL Server / Oracle / MS Access

CREATE TABLE Employee (
  EmpID INT NOT NULL,
  Name VARCHAR(255),
  City VARCHAR(100)   DEFAULT 'London',
  Age INT,
  Salary DECIMAL(18,2)
);

SQL DEFAULT constraint with ALTER TABLE statement

In the above example, a table called Employee is created. To enforce DEFAULT constraint on City column, the SQL code is given below:

MySQL

ALTER TABLE Employee
ALTER City SET DEFAULT 'London';

SQL Server

ALTER TABLE Employee
ADD CONSTRAINT DV_City
DEFAULT 'London' FOR City;

Oracle

ALTER TABLE Employee
MODIFY City DEFAULT 'London';

MS Access

ALTER TABLE Employee
ALTER COLUMN City SET DEFAULT 'London';

DROP DEFAULT constraint

To drop DEFAULT constraint from table called Employee, the SQL code is given below:

MySQL

ALTER TABLE Employee
ALTER City DROP DEFAULT;

SQL Server / Oracle / MS Access

ALTER TABLE Employee
ALTER COLUMN City DROP DEFAULT;