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

flowchart LR
  A[Insert omits column] --> B{Column has DEFAULT?}
  B -->|Yes| C[Use default value]
  B -->|No| D[Use NULL or fail if NOT NULL]

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.