MySQL - DEFAULT
The MySQL 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.
MySQL DEFAULT constraint with CREATE TABLE
The below mentioned query creates a table called Employee which contains five columns: EmpID, Name, City, Age and Salary in which DEFAULT constraint is applied on column City.
CREATE TABLE Employee ( EmpID INT NOT NULL, Name VARCHAR(255), City VARCHAR(100) DEFAULT 'London', Age INT, Salary DECIMAL(18,2) );
The DEFAULT constraint can also be used to insert system values, by using functions like CURRENT_DATE():
CREATE TABLE Orders ( OrderID INT NOT NULL, OrderQuantity INT NOT NULL, OrderPrice DECIMAL(18,2), OrderDate date DEFAULT CURRENT_DATE() );
MySQL DEFAULT constraint with ALTER TABLE
In the above example, a table called Employee is created. To enforce DEFAULT constraint on City column, the query is given below:
ALTER TABLE Employee ALTER City SET DEFAULT 'London';
DROP DEFAULT constraint
To drop DEFAULT constraint from table called Employee, the query is given below:
ALTER TABLE Employee ALTER City DROP DEFAULT;