SQL DEFAULT Constraint

The DEFAULT constraint lets you specify a fallback value for a column that the database engine automatically uses whenever an INSERT statement doesn’t supply a value for that column. Instead of silently ending up with a NULL, the column gets populated with something sensible — a status of 'Pending', a stock count of 0, or the current timestamp — without every application, script, or developer needing to remember to supply it. This keeps your data consistent and removes boilerplate from the application layer.

Overview: How the DEFAULT Constraint Works

A DEFAULT constraint is attached to a single column, right in its definition, when you run CREATE TABLE (or later, when you run ALTER TABLE ... ADD COLUMN). The database engine stores this default alongside the column’s data type and other constraints in its internal schema catalog — in SQLite this lives in the sqlite_master table; in other systems it’s an information_schema or system catalog entry.

The default is only consulted at INSERT time, and only when a column is omitted from the statement. Concretely, the engine builds the list of columns you provided, and for every column in the table that you did not mention, it looks up that column’s stored default expression and substitutes it into the new row before the row is validated against NOT NULL and CHECK constraints. If the default itself would violate a NOT NULL or CHECK constraint, the whole insert fails — the engine does not treat defaults as exempt from other rules.

An important nuance: DEFAULT only kicks in when a value is missing, not when you explicitly supply NULL. Writing NULL is an explicit value, so the engine uses it as-is and the default is never consulted. This trips up a lot of developers — see the Common Mistakes section below.

A default value can be a literal (a number, string, or NULL), a built-in date/time keyword such as CURRENT_TIMESTAMP, CURRENT_DATE, or CURRENT_TIME (evaluated fresh at insert time, not when the table was created), or in SQLite, PostgreSQL, and SQL Server, a parenthesized expression.

Syntax

CREATE TABLE table_name (
    column_name data_type DEFAULT default_value,
    ...
);

-- Adding a default to a new column on an existing table
ALTER TABLE table_name
ADD COLUMN column_name data_type DEFAULT default_value;

Part	Meaning
column_name	The column the default applies to. Exactly one default per column.
data_type	The column’s declared type (e.g. TEXT, INTEGER, REAL).
DEFAULT default_value	The value substituted when the column is omitted from an INSERT. Can be a literal, NULL, CURRENT_TIMESTAMP/CURRENT_DATE/CURRENT_TIME, or an expression in parentheses.

Dialect note: SQLite and PostgreSQL use ALTER TABLE ... ADD COLUMN ... DEFAULT ... to add a defaulted column. MySQL uses the same ADD COLUMN form but also supports ALTER TABLE t ALTER COLUMN c SET DEFAULT val to change an existing column’s default; PostgreSQL supports that same SET DEFAULT form too. SQLite has no ALTER COLUMN ... SET DEFAULT — to change an existing column’s default in SQLite you must recreate the table (see Common Mistakes).

Examples

Example 1: Basic defaults for missing values

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT DEFAULT 'Uncategorized',
    stock INTEGER DEFAULT 0
);

INSERT INTO products (product_id, product_name)
VALUES (1, 'Widget');

INSERT INTO products (product_id, product_name, category)
VALUES (2, 'Gadget', 'Electronics');

INSERT INTO products (product_id, product_name, category, stock)
VALUES (3, 'Gizmo', 'Hardware', 50);

SELECT * FROM products;

Result:

product_id	product_name	category	stock
1	Widget	Uncategorized	0
2	Gadget	Electronics	0
3	Gizmo	Hardware	50

Row 1 omitted both category and stock, so both defaults fired. Row 2 supplied category explicitly but let stock default to 0. Row 3 supplied everything, so no defaults were needed.

Example 2: Defaulting to the current timestamp

CREATE TABLE orders_demo (
    order_id INTEGER PRIMARY KEY,
    customer_name TEXT NOT NULL,
    order_status TEXT DEFAULT 'Pending',
    created_at TEXT DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO orders_demo (order_id, customer_name) VALUES (1, 'Alice');

SELECT order_id, customer_name, order_status FROM orders_demo;

Result:

order_id	customer_name	order_status
1	Alice	Pending

The row also received a created_at value equal to the current UTC date and time (e.g. 2026-07-19 14:32:07), computed at the moment of insertion — it’s omitted from this result set only because a timestamp changes every time the query runs, so it isn’t shown as a fixed value here. The key point: CURRENT_TIMESTAMP is re-evaluated for every row inserted, not fixed once at table-creation time.

Example 3: Inserting an all-default row

CREATE TABLE settings (
    setting_id INTEGER PRIMARY KEY,
    theme TEXT DEFAULT 'light',
    notifications INTEGER DEFAULT 1
);

INSERT INTO settings DEFAULT VALUES;

SELECT * FROM settings;

Result:

setting_id	theme	notifications
1	light	1

The DEFAULT VALUES clause inserts a single new row using every column’s default (and, for the INTEGER PRIMARY KEY, SQLite’s normal auto-assigned rowid). This is handy for tables like settings or flags where a brand-new record should always start in a known baseline state.

How It Works Step by Step / Under the Hood

When the engine processes an INSERT, it effectively does the following, in order: (1) resolve the explicit column list and values you supplied; (2) for every column you left out, look up its stored default expression in the schema and evaluate it — literals are used as-is, while CURRENT_TIMESTAMP-style keywords are evaluated fresh, right now; (3) assemble the full candidate row; (4) validate NOT NULL, CHECK, and other constraints against that assembled row, including the substituted defaults; (5) if everything passes, write the row.

A subtler piece of internals: when you add a new column to an existing table with ALTER TABLE ... ADD COLUMN ... DEFAULT ..., SQLite does not necessarily rewrite every existing row on disk. For a constant default, SQLite records the default in the schema and simply returns that default value whenever an older row (one that predates the column) is read, without physically touching the row’s stored bytes. The effect looks identical to a real backfill from the outside — every pre-existing row appears to have the new value — but it’s cheaper because no full-table rewrite is required. Other engines vary: PostgreSQL 11+ does the same lazy trick for non-volatile defaults, while older PostgreSQL and MySQL versions physically rewrite the whole table.

ALTER TABLE employees ADD COLUMN status TEXT DEFAULT 'Active';

SELECT id, name, status FROM employees;

Result: all 4 existing rows in employees now show status = 'Active' for the newly added column, even though the column didn’t exist when those rows were originally inserted — their id and name values are unchanged.

Common Mistakes

Mistake 1: Assuming an explicit NULL still gets the default

CREATE TABLE products (
    product_id INTEGER PRIMARY KEY,
    product_name TEXT NOT NULL,
    category TEXT DEFAULT 'Uncategorized'
);

INSERT INTO products (product_id, product_name, category)
VALUES (1, 'Sprocket', NULL);

SELECT product_id, product_name, category FROM products;

Result: category comes back as NULL, not 'Uncategorized'. Writing NULL explicitly is a supplied value, so the DEFAULT constraint never runs. If you want the default applied, simply omit the column from the INSERT column list instead of passing NULL.

Mistake 2: Trying to change a default with unsupported syntax

ALTER TABLE products ALTER COLUMN category SET DEFAULT 'Unknown';

This is valid in MySQL and PostgreSQL, but SQLite has no ALTER COLUMN ... SET DEFAULT clause, so this statement fails with a syntax error. To change a column’s default in SQLite you must recreate the table (create a new table with the desired schema, copy the data across with INSERT INTO new_table SELECT ... FROM old_table, drop the old table, and rename the new one) — there’s no shortcut.

Best Practices

• Use DEFAULT for values that have an obvious, safe baseline (status flags, counters starting at zero, creation timestamps) so application code doesn’t have to supply them every time.
• Prefer omitting a column over passing NULL when you want the default to apply — they are not interchangeable.
• Pair DEFAULT with NOT NULL when a column should never actually be empty; the default guarantees a valid value is always present even if the caller forgets it.
• Use CURRENT_TIMESTAMP/CURRENT_DATE defaults for audit columns like created_at instead of relying on application code to stamp the time, so the value is consistent even with multiple client languages or time zones.
• Keep defaults simple and predictable; avoid defaults that depend on volatile external state, since some engines (like PostgreSQL) disallow non-constant defaults for fast, metadata-only ADD COLUMN.
• Document non-obvious defaults (e.g. why a default is -1 instead of 0) since they aren’t visible in application code that omits the column.

Practice Exercises

Exercise 1: Create a table named subscriptions with columns id, plan_name, is_active (default 1), and signup_date (default CURRENT_DATE). Insert two rows supplying only id and plan_name, then write a query to confirm both defaults were applied.

Exercise 2: Using the employees table, write an ALTER TABLE statement that adds a country column defaulting to 'USA'. Then select id, name, and country to confirm every existing employee row shows the new default.

Exercise 3: Predict, then verify, what happens if you insert a row into the subscriptions table from Exercise 1 while explicitly passing NULL for is_active. Does it become 1 or stay NULL?

Summary

• DEFAULT supplies a fallback value used only when a column is omitted from an INSERT, not when NULL is explicitly supplied.
• Defaults can be literals, NULL, date/time keywords like CURRENT_TIMESTAMP, or (in SQLite/PostgreSQL/SQL Server) parenthesized expressions.
• Defaults are evaluated at insert time, validated against NOT NULL/CHECK constraints just like any other value, and stored in the table’s schema.
• ALTER TABLE ... ADD COLUMN ... DEFAULT ... makes existing rows appear to have the default value, often without a full table rewrite.
• INSERT INTO table DEFAULT VALUES inserts a row using every column’s default in one step.
• SQLite cannot change an existing column’s default in place; MySQL and PostgreSQL support ALTER COLUMN ... SET DEFAULT, but SQLite requires recreating the table.