MySQL DEFAULT Constraint

The DEFAULT constraint in MySQL assigns an automatic value to a column when no value is provided during an insert operation. Instead of leaving the field empty or causing an error, MySQL fills in the predefined default value.

Why Use DEFAULT?

Many columns have a common or expected value in most cases. For example, the status of a new order is almost always "Pending," or the country for a regional app is usually "India." Setting a default value avoids repetitive data entry and ensures the column always has a meaningful value.

Syntax

At Table Creation

CREATE TABLE table_name (
    column_name datatype DEFAULT default_value
);

Using ALTER TABLE

ALTER TABLE table_name
ALTER column_name SET DEFAULT default_value;

Example: Orders Table with DEFAULT Status

CREATE TABLE orders (
    order_id INT PRIMARY KEY,
    product_name VARCHAR(100) NOT NULL,
    quantity INT DEFAULT 1,
    status VARCHAR(20) DEFAULT 'Pending',
    order_date DATE DEFAULT (CURRENT_DATE)
);

Insert Without Providing Defaults

INSERT INTO orders (order_id, product_name)
VALUES (1, 'Wireless Mouse');

MySQL automatically fills in:

• quantity → 1
• status → Pending
• order_date → today's date

Viewing the Result

SELECT * FROM orders;

order_id | product_name    | quantity | status  | order_date
---------+-----------------+----------+---------+-----------
1        | Wireless Mouse  | 1        | Pending | 2024-05-10

Overriding the Default Value

The default value can be overridden by simply providing a value during insert.

INSERT INTO orders (order_id, product_name, quantity, status)
VALUES (2, 'Keyboard', 3, 'Shipped');

Here, quantity is 3 and status is "Shipped" instead of their defaults.

DEFAULT with Different Data Types

Numeric Default

rating INT DEFAULT 0

String Default

country VARCHAR(50) DEFAULT 'India'

Boolean Default

is_active TINYINT(1) DEFAULT 1

Date/Time Default

created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP

Removing a DEFAULT Value

ALTER TABLE orders
ALTER status DROP DEFAULT;

After this, if no value is given for status, MySQL will store NULL (if the column allows NULL) or return an error (if the column has NOT NULL).

DEFAULT with NOT NULL

Combining NOT NULL and DEFAULT ensures the column always has a value — either the one provided or the fallback default.

CREATE TABLE employees (
    emp_id INT PRIMARY KEY,
    emp_name VARCHAR(100) NOT NULL,
    department VARCHAR(50) NOT NULL DEFAULT 'General'
);

If no department is mentioned, it defaults to "General" rather than causing an error.

Key Points

• DEFAULT provides an automatic value for a column when no value is supplied.
• Default values can be strings, numbers, dates, or functions like CURRENT_DATE.
• Providing a value during insert always overrides the default.
• Combining NOT NULL and DEFAULT prevents NULL entries while reducing mandatory input.
• Use ALTER TABLE ... ALTER column DROP DEFAULT to remove a default value.