SQL Constraints Explained: NOT NULL, UNIQUE, CHECK and FOREIGN KEY, Tested

An app validates that a customer’s age is at least 18. Then a data import script, written months later by someone else, loads a file with ages of 0 and blank emails, and nothing stops it. Application validation protects one path into the database. Constraints protect all of them.

We built a small schema in SQLite 3.50 and deliberately inserted bad data. Every error message below is copied from that run. The constraint types are standard SQL; where other databases behave differently, we say so.

Why constraints matter

• One source of truth: rules live next to the data, not scattered across services.
• Defence in depth: imports, admin tools, migrations and bugs cannot bypass them.
• Self-documenting: the schema tells readers what valid data looks like.
• Help the optimizer: uniqueness and NOT NULL can enable better query plans.

Constraints do not replace validation in your application, which gives users friendly messages. They are the last line of defence, in the same way transactions protect consistency.

Our test schema

CREATE TABLE customers (
  id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  age INT CHECK (age >= 18),
  status TEXT NOT NULL DEFAULT 'active'
         CHECK (status IN ('active', 'blocked'))
);

CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer_id INT NOT NULL REFERENCES customers(id) ON DELETE RESTRICT,
  qty INT NOT NULL CHECK (qty > 0),
  price INT NOT NULL CHECK (price >= 0),
  CONSTRAINT price_qty_sane CHECK (qty * price < 10000000)
);

NOT NULL: a value is required

INSERT INTO customers (age) VALUES (25);
-- NOT NULL constraint failed: customers.email

NOT NULL is the simplest and most useful constraint. Make every column NOT NULL unless “unknown” is a real, meaningful state. Fewer NULLs also means fewer surprises in queries, as our NULL values guide explains.

UNIQUE: no duplicates

INSERT INTO customers (email, age) VALUES ('asha@example.com', 30);   -- OK
INSERT INTO customers (email, age) VALUES ('asha@example.com', 40);
-- UNIQUE constraint failed: customers.email

INSERT INTO customers (email, age) VALUES ('ASHA@example.com', 40);   -- OK (!)

The exact duplicate was rejected, but the same address in capitals was accepted, because the comparison is case-sensitive here. For emails, normalise to lower case before saving, or enforce uniqueness on lower(email) with a unique index on an expression where your database supports it. UNIQUE also creates an index behind the scenes, which speeds up lookups on that column.

A UNIQUE constraint can span several columns, such as UNIQUE (order_id, product_id), meaning the pair must be unique while each column alone may repeat.

CHECK: any rule you can express

INSERT INTO customers (email, age) VALUES ('kid@example.com', 15);
-- CHECK constraint failed: age >= 18

INSERT INTO customers (email, age, status) VALUES ('x@example.com', 20, 'deleted');
-- CHECK constraint failed: status IN ('active','blocked')

INSERT INTO orders (customer_id, qty, price) VALUES (1, 0, 10);
-- CHECK constraint failed: qty > 0

CHECK accepts any expression on the row’s columns: ranges, allowed values, relationships between columns such as end_date >= start_date, or format rules. It works only within a single row; rules that involve other rows or tables need foreign keys, unique constraints or application logic. Older MySQL versions parsed CHECK but ignored it; MySQL 8.0.16 and later enforce it, so check your version.

DEFAULT: a value when none is given

SELECT status FROM customers WHERE id = 1;   -- ('active',)

We never supplied a status, so the default filled it in, and that default still had to pass the CHECK. DEFAULT is not strictly a constraint, but it pairs naturally with NOT NULL: required, but with a sensible starting value. Common defaults include timestamps such as CURRENT_TIMESTAMP, status flags and counters.

FOREIGN KEY: references must exist

INSERT INTO orders (customer_id, qty, price) VALUES (99, 1, 10);
-- FOREIGN KEY constraint failed

DELETE FROM customers WHERE id = 1;     -- customer has orders
-- FOREIGN KEY constraint failed

A foreign key ensures every order points to a real customer, and, with ON DELETE RESTRICT, stops you deleting a customer who still has orders. Other options are CASCADE, which deletes the orders too, and SET NULL, which keeps them but clears the link. Choose deliberately: CASCADE is convenient, but one delete can remove far more than intended. Our primary vs foreign key guide covers the relationship in depth, and remember to index foreign key columns, since joins and these checks use them.

The NULL loopholes

INSERT INTO customers (email, age) VALUES ('noage@example.com', NULL);   -- OK

CREATE TABLE u (code TEXT UNIQUE);
INSERT INTO u VALUES (NULL);
INSERT INTO u VALUES (NULL);    -- both accepted: 2 rows

Our age rule, CHECK (age >= 18), accepted NULL. A CHECK only fails when its condition is false; with NULL the result is unknown, and unknown passes. UNIQUE accepted two NULLs because NULL is not equal to NULL. This is standard behaviour in most databases, though SQL Server allows only one NULL in a UNIQUE column, and PostgreSQL 15 added NULLS NOT DISTINCT to change it. If a value is required, add NOT NULL; CHECK and UNIQUE alone will not enforce it.

SQLite gotchas

Foreign keys are off by default

-- new connection, no PRAGMA
PRAGMA foreign_keys;                   -- 0
INSERT INTO o (cid) VALUES (99);       -- orphan row accepted
PRAGMA foreign_keys = ON;
PRAGMA foreign_key_check;              -- [('o', 1, 'c', 0)]

On a fresh connection, SQLite accepted an order for a customer that does not exist. Foreign key enforcement must be turned on with PRAGMA foreign_keys = ON for every connection, and it cannot be changed in the middle of a transaction. PRAGMA foreign_key_check finds existing orphans.

Type affinity

CREATE TABLE loose (n INT);
INSERT INTO loose VALUES ('abc');   -- accepted, stored as text

CREATE TABLE strict_t (n INT) STRICT;
INSERT INTO strict_t VALUES ('abc');
-- cannot store TEXT value in INT column strict_t.n

A normal SQLite table stored the text “abc” in an INT column. STRICT tables, added in SQLite 3.37, enforce column types. Other databases enforce types by default.

Naming constraints

INSERT INTO orders (customer_id, qty, price) VALUES (1, 10000, 5000);
-- CHECK constraint failed: price_qty_sane

Named constraints give readable errors and make them easy to drop or change later with ALTER TABLE ... DROP CONSTRAINT price_qty_sane. Unnamed constraints get generated names in many databases, such as orders_check1 or long random strings, which make error messages and migrations harder to work with. Pick a convention, such as table_column_rule, and stick to it.

Adding constraints to existing tables

Adding a constraint to a table that already holds bad data fails, or in some databases succeeds only if you skip validation. The safe sequence is: find violating rows with a query, fix or remove them, then add the constraint, ideally in a reviewed migration. PostgreSQL’s NOT VALID option adds a constraint for new rows first and validates old rows later, reducing locking on big tables. SQLite cannot add most constraints with ALTER TABLE; the table must be rebuilt.

Testing constraints

Constraints are logic, and logic should be tested. For each rule, insert one valid row and one that should fail, and assert the error, exactly as we did above. Include NULLs, boundaries such as age 17, 18 and 19, case variations for unique text, and deletes of referenced rows. These are classic boundary values and negative tests, and they quickly reveal missing NOT NULLs and forgotten foreign key settings.

Final thoughts

Constraints are cheap insurance. NOT NULL on required columns, UNIQUE on natural identifiers, CHECK for ranges and allowed values, and foreign keys between related tables catch whole categories of bad data at the door. Remember the NULL loopholes, turn on foreign keys in SQLite, name your constraints, and test them with deliberately bad data.

Frequently asked questions

Keep learning