MySQL DEFAULT Constraint is used to set default values for a data column. When we add a new record and we are not inserting any value of a particular DEFAULT Constraint column, then the default value will specify.
Syntax for DEFAULT Constraint :-
column_name datatype DEFAULT default_value;
Note:- Default value of DEFAULT Constraint must be literal constant eg. a string or a number.
Note:- Default value of DEFAULT Constraint can be used to insert system values like CURRENT_DATE(), CURRENT_TIMESTAMP, NOW(), etc.
Note:- Default value does not affect current data of the table, It only applies to the new rows of the particular column which hold DEFAULT Constraint of the table.
Example:-
CREATE TABLE Employee(
ID INT AUTO_INCREMENT PRIMARY KEY,
Name varchar(500) NOT NULL,
Department varchar(255),
Sex varchar(5) DEFAULT ‘M”,
Location varchar(255)
);
Syntax for adding DEFAULT CONSTRAINT in existing table:-
ALTER TABLE table_name ALTER column_name SET DEFAULT default_name;
Example:- ALTER TABLE Emloyee ALTER Location SET DEFAULT CURRENT_TIMESTAMP;
Syntax for delete DEFAULT Constraint for existing table:-
ALTER TABLE table_name ALTER column_name DROP DEFAULT;
Example:- ALTER TABLE Emloyee ALTER Location DROP DEFAULT;