NOT NULL

NOT NULL is one of the most important data-quality constraints.

It ensures required fields are always present.

Core Concepts

flowchart TD
  A[Incoming column value] --> B{Column is NOT NULL?}
  B -->|No| C[Accept NULL or value]
  B -->|Yes| D{Value is NULL?}
  D -->|Yes| E[Reject change]
  D -->|No| F[Accept value]

Pattern	Example
Required text	name TEXT NOT NULL
Required numeric	amount REAL NOT NULL
Required + unique	email TEXT NOT NULL UNIQUE

Code Examples

-- Define required columns explicitly.
CREATE TABLE contacts (
  contact_id INTEGER PRIMARY KEY,
  full_name  TEXT NOT NULL,
  email      TEXT NOT NULL UNIQUE,
  phone      TEXT
);

Expected output
Table contacts created with two required columns.

-- Inspect notnull flags.
PRAGMA table_info(contacts);

cid	name	type	notnull	dflt_value	pk
0	contact_id	INTEGER	0	null	1
1	full_name	TEXT	1	null	0
2	email	TEXT	1	null	0
3	phone	TEXT	0	null	0

SQLite-Specific Nuances

SQLite Nuance

In SQLite, an empty string ('') is not NULL.

If empty strings are also invalid, add a CHECK(length(trim(column)) > 0) rule.

Common Pitfalls / Best Practices

Pitfall

Treating NOT NULL as optional during schema design and planning to "enforce in app code later".

Application-only checks are easier to bypass accidentally.

Best Practice

Mark truly required columns as NOT NULL at schema level, then add app validation for user-friendly errors.

Quick Challenge

Create a payments table with required amount and paid_at columns.

View Solution

CREATE TABLE payments (
  payment_id INTEGER PRIMARY KEY,
  amount     REAL NOT NULL,
  paid_at    TEXT NOT NULL
);

Expected output
Table created with required amount and timestamp fields.