SQL DEFAULT Constraint Explained

The DEFAULT constraint lets you specify a fallback value for a column — one that gets used automatically whenever an INSERT doesn't provide a value for it. It's a small feature that removes a surprising amount of repetitive application logic, especially for things like status flags and timestamps.

Basic Syntax

Here's an orders table where new orders should start out as 'pending' unless told otherwise:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending'
);

-- status isn't provided, so it defaults to 'pending'
INSERT INTO orders (id, customer_id, amount) VALUES (501, 1, 120.00);

SELECT * FROM orders WHERE id = 501;

Result:

id | customer_id | amount | status
----+-------------+--------+--------
501 | 1 | 120.00 | pending

Defaulting a Timestamp to "Now"

One of the most common uses of DEFAULT is automatically stamping a row with the time it was created, so your application never has to compute that itself:

CREATE TABLE orders (
    id INT PRIMARY KEY,
    customer_id INT,
    amount DECIMAL(10, 2),
    status VARCHAR(20) DEFAULT 'pending',
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Every row now automatically records exactly when it was inserted, without a single extra line in whatever application code is doing the inserting.

Overriding the Default

A default is just that — a fallback. You can still explicitly provide your own value, and it takes priority:

INSERT INTO orders (id, customer_id, amount, status)
VALUES (502, 2, 75.00, 'shipped');

Result:

id | customer_id | amount | status
----+-------------+--------+---------
502 | 2 | 75.00 | shipped

DEFAULT pairs naturally with a NOT NULL constraint — together they guarantee a column is always populated, either with the value you supply or a sensible fallback, which keeps your INSERT statements shorter and less error-prone.

---