Default Values

DEFAULT gives a column an automatic value when no explicit value is provided.

This keeps inserts simpler and creates predictable baseline data.

Core Concepts

Default type	Example	Common use
Literal text	DEFAULT 'draft'	Status fields
Numeric literal	DEFAULT 0	Flags/counters
Timestamp expression	DEFAULT CURRENT_TIMESTAMP	Created time

Literal Defaults

CREATE TABLE tasks (
  task_id   INTEGER PRIMARY KEY,
  title     TEXT NOT NULL,
  status    TEXT DEFAULT 'todo',
  priority  INTEGER DEFAULT 1
);

Expected output
Table created with text and numeric default values.

Timestamp Default

CREATE TABLE notes (
  note_id     INTEGER PRIMARY KEY,
  body        TEXT NOT NULL,
  created_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

Expected output
Table created with timestamp default expression.

Code Examples

-- Inspect defaults in table metadata.
PRAGMA table_info(tasks);

cid	name	type	notnull	dflt_value	pk
0	task_id	INTEGER	0	null	1
1	title	TEXT	1	null	0
2	status	TEXT	0	'todo'	0
3	priority	INTEGER	0	1	0

SQLite-Specific Nuances

SQLite Nuance

A default is used only when the column is omitted in an insert statement.

If a query explicitly supplies NULL, SQLite treats that as an explicit value.

Common Pitfalls / Best Practices

Pitfall

Assuming defaults are retroactively applied to existing rows.

Defaults affect row creation behavior, not automatic backfills.

Best Practice

Use defaults for common initial states (active = 1, status = 'pending') to reduce repeated SQL.

Quick Challenge

Create an issues table with:

• issue_id primary key
• title required
• state default 'open'
• created_at default CURRENT_TIMESTAMP

View Solution

CREATE TABLE issues (
  issue_id    INTEGER PRIMARY KEY,
  title       TEXT NOT NULL,
  state       TEXT DEFAULT 'open',
  created_at  TEXT DEFAULT CURRENT_TIMESTAMP
);

Expected output
Table created with both literal and expression defaults.