MySQL DEFAULT Constraint

The DEFAULT constraint lets you specify a fallback value for a column. When a new row is inserted and that column is not included in the INSERT statement, MySQL automatically uses the default value instead of leaving it empty or storing NULL.

This is useful for fields that have a predictable starting value — such as a status set to 'active', a counter starting at 0, or a timestamp recording when a record was created.

---

Syntax

column_name data_type DEFAULT default_value

---

Using DEFAULT in CREATE TABLE

The example below creates an orders table where the status defaults to 'pending' and the created date defaults to the current date and time:

CREATE TABLE orders (
    id INT NOT NULL AUTO_INCREMENT,
    customer_name VARCHAR(100) NOT NULL,
    total DECIMAL(10, 2) NOT NULL,
    status VARCHAR(20) DEFAULT 'pending',
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

---

DEFAULT in Action

When you insert a row without specifying status or created_at, MySQL fills them in automatically:

INSERT INTO orders (customer_name, total)
VALUES ('Rahim Khan', 3500.00);

The stored row will look like this:

id	customer_name	total	status	created_at
1	Rahim Khan	3500.00	pending	2025-06-01 10:45:00

---

Explicitly Using the DEFAULT Keyword

You can also use the DEFAULT keyword directly inside an INSERT statement to explicitly trigger the default value, even when you are listing all columns:

INSERT INTO orders (customer_name, total, status, created_at)
VALUES ('Sara Begum', 1200.00, DEFAULT, DEFAULT);

This is useful in scripts where column order must be explicit but you still want the defaults to apply.

---

Overriding a Default Value

Supplying a value for the column in the INSERT statement always overrides the default:

INSERT INTO orders (customer_name, total, status)
VALUES ('Karim Ali', 800.00, 'shipped');

Here the status is set to 'shipped' instead of the default 'pending'.

---

DEFAULT with Different Data Types

You can apply DEFAULT to most data types:

CREATE TABLE settings (
    id INT NOT NULL AUTO_INCREMENT,
    theme VARCHAR(20) DEFAULT 'light',
    font_size INT DEFAULT 14,
    notifications TINYINT DEFAULT 1,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
    PRIMARY KEY (id)
);

---

Adding or Changing a DEFAULT on an Existing Column

Use ALTER TABLE with MODIFY COLUMN to set or update a default value on a column that already exists:

ALTER TABLE orders
MODIFY COLUMN status VARCHAR(20) DEFAULT 'processing';

This changes the default for all future inserts. Rows already in the table are not affected.

---

Removing a DEFAULT Value

To remove a default so the column has no fallback value, use ALTER TABLE ... ALTER COLUMN ... DROP DEFAULT:

ALTER TABLE orders
ALTER COLUMN status DROP DEFAULT;

After this, if status is not provided in an INSERT and no NOT NULL constraint is set, the column will store NULL.

---

Key Points to Remember

• DEFAULT provides an automatic fallback value when a column is omitted from an INSERT.
• It works with strings, numbers, dates, and functions like CURRENT_TIMESTAMP.
• You can override a default by explicitly supplying a different value in the INSERT.
• Use the DEFAULT keyword in INSERT statements to explicitly trigger the default value.
• ON UPDATE CURRENT_TIMESTAMP keeps a timestamp column auto-updated whenever the row changes.
• Changing a default with ALTER TABLE only affects new rows — existing rows are not changed.