What are NOT NULL, DEFAULT and other column constraints?

Q: What are NOT NULL, DEFAULT and other column constraints?

**Target Audience:** Beginners writing their first CREATE TABLE, and candidates who need the migration details that come up in senior interviews. **Difficulty:** Easy **Engines:** SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Version facts are cited from the PostgreSQL and SQLite release notes. > **How to read this doc:** Every idea is explained in plain language first, then tagged with `📌 Interview term:` so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the **Verified** section quotes the example output in full. ## 1. Why This Even Matters — A Story First Think of a paper form at a doctor's surgery. Some boxes have a red star: *name* and *date of birth* must be filled in, or the receptionist hands the form back. Some boxes come pre-printed: *Preferred contact: email* is already ticked, unless you tick something else. On

The problem

Question presented to candidate: "Walk me through the column constraints you can put in a CREATE TABLE: NOT NULL, DEFAULT and the others. For each, tell me what the database enforces, and what changes when you add one to a table that already has data."

What a strong answer should cover:

NOT NULL rejects missing values; it is the most common constraint and the one CHECK and UNIQUE do not replace
DEFAULT is not a constraint on the data: it only supplies a value when the column is omitted (or written as DEFAULT); an explicit NULL is stored as NULL
The rest of the family: PRIMARY KEY, UNIQUE, CHECK, REFERENCES (foreign key), plus generated columns and identity columns, and column vs table constraints
Naming constraints with CONSTRAINT name so errors and migrations are readable
Adding to a live table: SET NOT NULL fails if NULLs exist; ADD COLUMN ... NOT NULL needs a DEFAULT; PostgreSQL 11+ adds a constant default without rewriting the table
Engine differences: SQLite INTEGER PRIMARY KEY auto-assigns ids, PostgreSQL uses identity columns; SQLite cannot add a column with a non-constant default

Code / implementation expected: Optional — a CREATE TABLE using each constraint, and inserts that show a default being applied versus an explicit NULL.

Schema used by the examples:

constraints

Try it yourself

Sketch your approach before you read the solution. That is what the interview tests.

Stuck? Get a hint.

A short nudge toward the approach. It will not give the answer away.

Solution

Target Audience: Beginners writing their first CREATE TABLE, and candidates who need the migration details that come up in senior interviews. Difficulty: Easy Engines: SQLite 3.45.0 (sql.js 1.10.2, the engine the runnable examples on this page use) and PostgreSQL 16.15, both run for this page. Version facts are cited from the PostgreSQL and SQLite release notes.

How to read this doc: Every idea is explained in plain language first, then tagged with 📌 Interview term: so you also learn the exact words an interviewer expects. Every result and error message shown was produced by actually running it; the Verified section quotes the example output in full.

1. Why This Even Matters — A Story First

Think of a paper form at a doctor's surgery. Some boxes have a red star: name and date of birth must be filled in, or the receptionist hands the form back. Some boxe

The solution is hidden so you can try first

13 min read · 11 sections

Run the code

Edit the query and run it against the tables for this question.

SQLOmit a column and its DEFAULT fills it; write NULL and NULL is stored

SQLThe constraints SQLite stored for each column

SQLADD COLUMN ... NOT NULL DEFAULT gives every existing row the default

PostgreSQL: the DEFAULT keyword inside VALUES, and RETURNING to see what was stored

INSERT INTO tickets (id, title, priority, channel)
VALUES (20, 'Laptop will not boot', DEFAULT, DEFAULT)
RETURNING id, priority, status, channel, assignee, slug;

Discussion

Other approaches, follow-ups and how it went in real interviews.

Sign in to join the discussion.

No comments yet. Share how you would answer it.

The solution is hidden so you can try first

Keep your momentum going.