Loading

MySQL DEFAULT Constraint

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;

Share with:


Leave a Reply

Connect with:

z35W7z4v9z8w