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;