DEFAULT

What DEFAULT Does

DEFAULT supplies a value automatically for a column when an INSERT statement omits that column entirely. It is a convenience/consistency feature, not an integrity constraint — it never rejects a statement; it just fills in a gap.

Literal Defaults

sql
CREATE TABLE products (
    product_id INT PRIMARY KEY,
    in_stock   BOOLEAN DEFAULT TRUE,
    quantity   INT DEFAULT 0,
    status     VARCHAR(20) DEFAULT 'pending'
);

sql
INSERT INTO products (product_id) VALUES (1);
-- in_stock = TRUE, quantity = 0, status = 'pending' automatically

Expression / Function Defaults

sql
CREATE TABLE orders (
    order_id   INT PRIMARY KEY,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    updated_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CURRENT_TIMESTAMP (also NOW() in MySQL, SYSDATE in Oracle) is evaluated fresh at the moment each row is inserted, not fixed at table-creation time. MySQL additionally supports ON UPDATE CURRENT_TIMESTAMP on a column so it auto-refreshes on every update — a MySQL-specific extension, not standard SQL.

The Critical Rule: DEFAULT vs Explicit NULL

sql
INSERT INTO products (product_id, quantity) VALUES (2, NULL);
-- quantity is explicitly set to NULL — DEFAULT does NOT apply here

DEFAULT only fires when a column is left out of the column list (or, in some dialects, when the literal keyword DEFAULT is used in the VALUES list: VALUES (2, DEFAULT)). Explicitly passing NULL always wins over any DEFAULT clause — the two are not interchangeable. This means quantity INT DEFAULT 0 will still end up NULL in that row unless the column is also NOT NULL, in which case the explicit-NULL insert would instead fail outright.

Adding/Changing a DEFAULT on an Existing Table

sql
ALTER TABLE products ALTER COLUMN status SET DEFAULT 'active';   -- PostgreSQL
ALTER TABLE products ALTER status SET DEFAULT 'active';          -- SQL Server
ALTER TABLE products MODIFY status VARCHAR(20) DEFAULT 'active'; -- MySQL

Crucially, changing a DEFAULT never retroactively updates existing rows — it only affects future inserts that omit the column.

Dropping a DEFAULT

sql
ALTER TABLE products ALTER COLUMN status DROP DEFAULT;   -- PostgreSQL

Edge Cases

• A DEFAULT expression referencing a non-deterministic function (like RANDOM() or CURRENT_TIMESTAMP) produces a different value per row, which is fine and common, but a DEFAULT cannot reference other columns of the same row in standard SQL (no DEFAULT (price * 1.1) referencing price) — that requires a generated/computed column instead.
• DEFAULT and NOT NULL combine well: DEFAULT handles the "column omitted" case; NOT NULL guards the "explicit NULL" case, but only if both are declared together.
• Auto-increment mechanisms (SERIAL, AUTO_INCREMENT, IDENTITY) are conceptually a specialized DEFAULT-like mechanism but are usually a separate keyword/feature, not the DEFAULT clause itself.

Key Takeaways / Q&A

Q: If a column has DEFAULT 0 and NOT NULL, and an INSERT explicitly supplies NULL for it, what happens? A: The insert fails with a NOT NULL violation — DEFAULT never rescues an explicit NULL.

Q: Does changing a column's DEFAULT value update existing rows? A: No, only future inserts that omit the column are affected; existing stored values are untouched.

Q: Is CURRENT_TIMESTAMP evaluated once when the table is created, or per row? A: Per row, at the moment each individual row is inserted.