DEFAULT Values
A DEFAULT clause specifies a fallback value that the database automatically fills in for a column when an INSERT statement does not explicitly provide one. Rather than requiring every INSERT to spell out every column, columns with sensible defaults can simply be omitted, and the database fills in the standard value on their behalf.
Declaring a default
CREATE TABLE orders ( order_id INT PRIMARY KEY, customer_id INT NOT NULL, status VARCHAR(20) NOT NULL DEFAULT 'pending', created_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );
-- status and created_at are omitted, so their defaults are used INSERT INTO orders (order_id, customer_id) VALUES (1001, 42);
order_id | customer_id | status | created_at ---------+-------------+---------+--------------------- 1001 | 42 | pending | 2024-06-01 14:23:07
The application only had to supply order_id and customer_id — status was automatically set to 'pending', and created_at was stamped with the exact moment the row was inserted, both without the INSERT statement mentioning them at all.
DEFAULT CURRENT_TIMESTAMP
One of the most common DEFAULT patterns is automatically stamping a row with the time it was created, using CURRENT_TIMESTAMP (equivalent to NOW() in several dialects):
CREATE TABLE audit_log ( log_id INT PRIMARY KEY, action VARCHAR(50) NOT NULL, logged_at TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP );
Every row automatically records when it was inserted, with no application code needing to generate or pass a timestamp — which also avoids clock-skew bugs where different application servers might otherwise stamp rows using slightly different local times.
DEFAULT can still be overridden
A DEFAULT value is only used when a column is left out of the INSERT's column list entirely. If the INSERT explicitly supplies a value — even NULL, if the column allows it — that value is used instead of the default.
-- Explicitly overrides the default status INSERT INTO orders (order_id, customer_id, status) VALUES (1002, 43, 'shipped');
DEFAULT vs NOT NULL
These two are easy to conflate but solve different problems. NOT NULL is a validation rule — it rejects an INSERT that would leave the column empty. DEFAULT is a convenience — it supplies a value automatically so the column is never left empty in the first place, even though the application didn't specify one.
NOT NULL | DEFAULT | |
|---|---|---|
Purpose | Rejects missing values | Supplies a value when none is given |
What happens if omitted from INSERT | Error, unless a DEFAULT is also present | The default value is used instead of an error |
Can be used together | Yes — a very common combination | Yes — a very common combination |
They combine naturally: a column can be NOT NULL DEFAULT 'pending', meaning the column can never be empty, and whenever it's not specified explicitly, it quietly becomes 'pending' instead of raising an error.