What is a CHECK constraint? | Interview Question

Q: What is a CHECK constraint?

**Target Audience:** Beginners who have written CREATE TABLE and want the database to refuse bad data, and candidates who need the NULL and engine details. **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. MySQL behaviour is cited from its manual. > **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 A bouncer at a club door has a short list of rules: you must be over 18, you must have a ticket for tonight. He checks every single person, every time, no matter which entrance they use or who sent them. He does not care whether the ticket office already checked; he checks again. A CHE

The problem

Question presented to candidate: "We keep finding negative prices and end dates before start dates in our products table. What is a CHECK constraint, how would you use one here, and what are its limits?"

What a strong answer should cover:

A CHECK constraint is a boolean expression every row must satisfy on INSERT and UPDATE; a row is rejected only when the expression is FALSE
A NULL (unknown) result passes, so CHECK does not replace NOT NULL
Column-level vs table-level CHECKs, and naming constraints so errors are readable
Limits: only the current row (no subqueries or other tables in PostgreSQL and SQLite); cross-row or cross-table rules need UNIQUE, foreign keys, exclusion constraints or triggers
Adding one to a live table: PostgreSQL NOT VALID then VALIDATE CONSTRAINT; SQLite cannot add one with ALTER TABLE
Engine facts: MySQL ignored CHECK before 8.0.16; SQLite type affinity can let a text value through a numeric check

Code / implementation expected: Yes — add CHECKs for price, a status list and a date window, and show what happens to a bad row and to a NULL.

Schema used by the examples:

check-constraint

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 who have written CREATE TABLE and want the database to refuse bad data, and candidates who need the NULL and engine details. 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. MySQL behaviour is cited from its manual.

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

A bouncer at a club door has a short list of rules: you must be over 18, you must have a ticket for tonight. He checks every single person, every time, no matter which entrance they use o

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.

SQLA CHECK only rejects FALSE: rows where the expression is NULL were accepted

SQLSQLite: INSERT OR IGNORE skips the rows that fail a CHECK and keeps the good one

SQLSQLite pitfall: the text value 'free' passes CHECK (price >= 0), because in SQLite any text compares greater than any number

PostgreSQL: add a CHECK to a live table with NOT VALID, then VALIDATE it separately

ALTER TABLE products ADD CONSTRAINT name_not_blank CHECK (length(trim(name)) > 0) NOT VALID;

SELECT conname, convalidated, pg_get_constraintdef(oid) AS definition
FROM pg_constraint
WHERE conrelid = 'products'::regclass AND contype = 'c'
ORDER BY conname;

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.