MySQL DEFAULT Constraint

MySQL DEFAULT Constraint: Supplying Automatic Column Values

The MySQL DEFAULT constraint specifies an automatic fallback value whenever an INSERT statement omits that column. Defaults reduce boilerplate in application code and ensure new columns added during migrations receive clean initial values.

Basic Syntax

Attach DEFAULT value directly inside CREATE TABLE or ALTER TABLE:

CREATE TABLE demo_orders (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    status VARCHAR(20) NOT NULL DEFAULT 'pending',
    currency CHAR(3) NOT NULL DEFAULT 'USD',
    created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

Practical SQL Examples

In MySQL 8.0.13+, you can even wrap dynamic expression defaults (including UUID(), JSON_OBJECT(), or CURRENT_DATE) inside parentheses:

CREATE TABLE demo_customers (
    id BIGINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(50) NOT NULL,
    tier VARCHAR(20) NOT NULL DEFAULT 'free',
    preferences JSON NOT NULL DEFAULT (JSON_OBJECT('newsletter', false)),
    joined_date DATE NOT NULL DEFAULT (CURRENT_DATE)
);

-- Update or drop a default later
ALTER TABLE demo_customers
    ALTER COLUMN tier SET DEFAULT 'standard';

Common Mistakes and Best Practices

• Understand Explicit NULL vs. Omitted Column: If a column is nullable and an INSERT explicitly passes NULL (INSERT INTO demo_customers (tier) VALUES (NULL)), MySQL inserts NULL—not the DEFAULT value. Make the column NOT NULL or pass the DEFAULT keyword (VALUES (DEFAULT)) instead.
• Use ON UPDATE CURRENT_TIMESTAMP for updated_at: Pair DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP on audit timestamp columns so edits stamp themselves automatically without extra backend logic.
• Avoid Mixing DEFAULT with AUTO_INCREMENT: An AUTO_INCREMENT primary key cannot also declare a DEFAULT clause.

Frequently Asked Questions

Can TEXT or JSON columns have a DEFAULT in MySQL 8?

Yes, starting in MySQL 8.0.13, BLOB, TEXT, GEOMETRY, and JSON columns support expression defaults wrapped in parentheses such as DEFAULT ('') or DEFAULT (JSON_ARRAY()).

Does adding a DEFAULT via ALTER TABLE ... SET DEFAULT change existing rows?

No, SET DEFAULT only applies to future INSERT statements; existing rows keep their current values.

What happens if you omit a NOT NULL column that has no DEFAULT?

In strict SQL mode (default in MySQL 8), the INSERT fails immediately with Field 'col' doesn't have a default value.