MYSQL Default Constraint

Overview

MYSQL Default Constraint is used to set default value for any column in a database table. Column gets populated with the default value if no value is specified while insertion.

Syntax

Syntax for setting default constraint while creating a table is

CREATE TABLE table_name (column1 data_type DEFAULT default_value, column2 data_type);

Syntax for setting default value in already created table is

ALTER TABLE table_name MODIFY column_name DEFAULT default_value;

Syntax for removing default value is

ALTER TABLE table_name ALTER COLUMN column_name DROP DEFAULT;

Examples

Let us look closer on this with the help of an example

Let us create a table users

CREATE TABLE users (id int(5) NOT NULL PRIMARY KEY, name varchar(200), age int(2), status tinyint(1) DEFAULT 1);

Case 1: 

Let us ALTER above created table and add a new column SALARY with default value of 10000.

ALTER TABLE users ADD salary int(7) DEFAULT 10000;

Case 2:

Let us now DROP default constraint from salary column.

ALTER TABLE users ALTER COLUMN salary DROP DEFAULT;