MySQL DEFAULT Constraint

The DEFAULT constraint in MySQL automatically assigns a default value to a column in a table. It provides the default value when no value is specified for that column in an INSERT statement.

The DEFAULT value can be a literal value, an expression, or a MySQL function.

Syntax of MySQL DEFAULT Constraint

The DEFAULT constraint is specified after the data type of a column while creating a table. It tells MySQL which value to use automatically when no value is provided for that column during data insertion.

The general syntax of DEFAULT constraint in MySQL while creating a table is:

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value
);

In this syntax:

• column_name: The name of the column.
• data_type: The type of data the column can store, such as INT, VARCHAR, DATE, etc.
• DEFAULT: A keyword that defines a default value for the column.
• default_value: The value that MySQL automatically assigns when no value is specified for the column.

Example 1:

CREATE TABLE Employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(50),
    country VARCHAR(30) DEFAULT 'India'
);

In this example, the country column has a default value of ‘India’. If you do not specify the country during data insertion, MySQL automatically inserts ‘India’.

Examples of DEFAULT Constraint in MySQL

Let’s take some important examples based on the usage of the DEFAULT constraint in MySQL.

Example 1: DEFAULT Constraint with String Value

Create a Table:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    city VARCHAR(30) DEFAULT 'Delhi'
);

Insert Data:

INSERT INTO Students(student_id, student_name)
VALUES(101, 'Rahul');

Display Records:

SELECT * FROM Students;

Output:

In this example, we have not provided a value for the city column; MySQL automatically inserted the default value Delhi.

Example 2: Providing a Value Overrides the DEFAULT Value

Create a Table:

CREATE TABLE Students (
    student_id INT PRIMARY KEY,
    student_name VARCHAR(50),
    city VARCHAR(30) DEFAULT 'Delhi'
);

Insert Record:

INSERT INTO Students
VALUES(102, 'Amit', 'Mumbai');

Display Records:

SELECT * FROM Students;

Output:

In this example, we have explicitly provided the value Mumbai. Therefore, MySQL ignored the default value.

Example 3: DEFAULT Constraint with Numeric Value

Create a Table:

CREATE TABLE Products (
    product_id INT PRIMARY KEY,
    product_name VARCHAR(50),
    quantity INT DEFAULT 10
);

Insert Data:

INSERT INTO Products(product_id, product_name)
VALUES(1, 'Laptop');

Display Records:

SELECT FROM Products;

Output:

In this example, we have not specified the quantity; the default value 10 is automatically inserted. Thus, you can also use the DEFAULT constraint in MySQL to assign boolean values (TRUE or FALSE), the current date, and the current timestamp as default values.

Adding DEFAULT Constraint to an Existing Table

Suppose we already have a table and we want to add a default value. The general syntax to add a default value into an existing table is:

ALTER TABLE table_name
ALTER column_name SET DEFAULT default_value;

Example 4:

ALTER TABLE Employees
ALTER country SET DEFAULT 'India';

Now, whenever we add a new record without specifying a country, MySQL automatically inserts India as the default value.