SQL Constraints

A constraint is a rule attached to a column, or to a table as a whole, that the database engine enforces automatically every time you insert, update, or delete data. Constraints are how you tell the database what counts as valid: a field that must always have a value, an identifier that must never repeat, a number that must stay inside a range, a foreign key that must point to a row that actually exists. Instead of relying on application code to catch bad data, you push that responsibility into the database itself, where it can never be skipped by a forgotten check in some script. This lesson walks through every major constraint type, how the engine enforces them, and the mistakes that trip up even experienced developers.

Overview: How Constraints Work

Constraints are declared when you create (or later alter) a table, and they live as metadata attached to the table’s schema. Every time a statement tries to write to that table – an INSERT, an UPDATE, sometimes a DELETE – the engine checks the incoming or resulting row against every constraint on the table before the write is committed. If any constraint fails, the entire statement is rejected and no partial change is made; in a multi-row INSERT, most engines abort the whole statement (SQLite does, by default) rather than silently skipping the bad row.

This matters because it moves data-integrity rules out of application code and into a single, shared place that every client – your web app, a batch script, an analyst running ad-hoc queries – must obey. A web form can have a bug that lets an empty string through; a constraint at the database layer cannot be bypassed that way. Constraints also help the query planner: a PRIMARY KEY or UNIQUE constraint automatically creates an index, which speeds up lookups and joins on that column, and a NOT NULL constraint lets the optimizer skip null-checking logic entirely.

There are six constraint types you’ll use constantly:

Constraint	Purpose
NOT NULL	Column must always have a value; rejects NULL
UNIQUE	No two rows may share the same value in this column (or column set)
PRIMARY KEY	Uniquely identifies each row; implies NOT NULL + UNIQUE
FOREIGN KEY	Value must match an existing value in another table’s referenced column
CHECK	Value must satisfy a boolean expression you define
DEFAULT	Supplies a value automatically when none is given (not strictly a validation rule, but grouped here)

Syntax

Constraints can be written at the column level (attached right after a column’s type) or at the table level (listed separately, usually needed when a constraint spans more than one column):

CREATE TABLE table_name (
    column1 TYPE CONSTRAINT,
    column2 TYPE CONSTRAINT,
    ...
    CONSTRAINT_NAME (column-level constraint spanning multiple columns)
);

• Column-level: email TEXT NOT NULL UNIQUE – constraints follow the type directly and apply to that one column.
• Table-level: PRIMARY KEY (student_id, course_code) – required for composite (multi-column) primary keys, unique constraints, or foreign keys, since a single column-level clause can only reference one column.
• CHECK expressions can reference one or more columns in the same row, e.g. CHECK (gpa >= 0.0 AND gpa <= 4.0).
• FOREIGN KEY clauses take the form FOREIGN KEY (local_column) REFERENCES other_table (other_column) and may add ON DELETE / ON UPDATE actions such as CASCADE, SET NULL, or RESTRICT.
• Naming a constraint with CONSTRAINT name ... is optional in SQLite but recommended in production databases, since a named constraint gives you a readable error message and something to reference later (e.g. ALTER TABLE ... DROP CONSTRAINT name in PostgreSQL or SQL Server).

Examples

Example 1: Column-level constraints together

CREATE TABLE students (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    last_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    gpa REAL CHECK (gpa >= 0.0 AND gpa <= 4.0),
    status TEXT DEFAULT 'active'
);

INSERT INTO students (student_id, first_name, last_name, email, gpa)
VALUES (1, 'Maria', 'Chen', 'maria.chen@example.com', 3.8);

INSERT INTO students (student_id, first_name, last_name, email, gpa)
VALUES (2, 'Tom', 'Reyes', 'tom.reyes@example.com', 3.2);

SELECT student_id, first_name, last_name, email, gpa, status
FROM students
ORDER BY student_id;

Result:

student_id	first_name	last_name	email	gpa	status
1	Maria	Chen	maria.chen@example.com	3.8	active
2	Tom	Reyes	tom.reyes@example.com	3.2	active

Neither INSERT supplied a status, so the DEFAULT 'active' filled it in automatically. Both rows satisfy NOT NULL, UNIQUE (the emails differ), and the CHECK range on gpa. If either email had been repeated or either gpa had been outside 0-4, the whole INSERT would have failed.

Example 2: Composite primary key (table-level constraint)

CREATE TABLE enrollments (
    student_id INTEGER,
    course_code TEXT,
    enrolled_date TEXT NOT NULL,
    grade TEXT,
    PRIMARY KEY (student_id, course_code)
);

INSERT INTO enrollments (student_id, course_code, enrolled_date, grade) VALUES
    (1, 'CS101', '2026-01-10', 'A'),
    (1, 'MATH201', '2026-01-10', 'B'),
    (2, 'CS101', '2026-01-12', NULL);

SELECT * FROM enrollments ORDER BY student_id, course_code;

Result:

student_id	course_code	enrolled_date	grade
1	CS101	2026-01-10	A
1	MATH201	2026-01-10	B
2	CS101	2026-01-12	NULL

No single column here is unique on its own – student 1 appears twice – but the combination of student_id and course_code is guaranteed unique by the table-level PRIMARY KEY. This is the standard pattern for a junction table linking two entities in a many-to-many relationship. Note that grade has no NOT NULL, so a student can be enrolled without a grade yet.

Example 3: FOREIGN KEY enforcing a real relationship

CREATE TABLE departments (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL UNIQUE
);

CREATE TABLE staff (
    staff_id INTEGER PRIMARY KEY,
    staff_name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments (department_id)
);

INSERT INTO departments (department_id, department_name) VALUES (1, 'Engineering'), (2, 'Marketing');

INSERT INTO staff (staff_id, staff_name, department_id) VALUES
    (1, 'Alice Kim', 1),
    (2, 'Ben Osei', 2),
    (3, 'Carla Diaz', 1);

SELECT staff.staff_name, departments.department_name
FROM staff
JOIN departments ON staff.department_id = departments.department_id
ORDER BY staff.staff_id;

Result:

staff_name	department_name
Alice Kim	Engineering
Ben Osei	Marketing
Carla Diaz	Engineering

The FOREIGN KEY on staff.department_id declares that any value there must exist as a department_id in departments. This is what makes the JOIN meaningful: every staff row is guaranteed to resolve to a real department, so you never get an orphaned row silently pointing at nothing.

How It Works Step by Step (Under the Hood)

When a write statement runs, the engine works through roughly this sequence:

• 1. Parse and resolve. The statement is parsed and the target table’s schema (including all its constraints) is loaded.
• 2. Compute the candidate row(s). For an INSERT, this means applying any DEFAULT values for columns you didn’t supply.
• 3. Check NOT NULL. Any column marked NOT NULL that would end up NULL fails immediately.
• 4. Check CHECK expressions. Each CHECK is evaluated against the candidate row; a FALSE result (not NULL – CHECK treats NULL as passing) aborts the statement.
• 5. Check UNIQUE / PRIMARY KEY. The engine consults the automatically-created index backing the constraint to see if the value(s) already exist.
• 6. Check FOREIGN KEY. The referenced table is probed for a matching row (in SQLite this step only runs if foreign key enforcement has been turned on for the connection – see Common Mistakes below).
• 7. Commit or abort. If every check passes, the row is written; if any fails, the whole statement is rolled back and an error is raised – no partial writes.

This ordering explains a subtlety worth remembering: a CHECK constraint doesn’t run against a value in isolation, it runs against the fully-defaulted candidate row, so DEFAULT values must themselves satisfy any CHECK on that column.

Common Mistakes

Mistake 1: Inserting NULL into a NOT NULL column

CREATE TABLE students_test (
    student_id INTEGER PRIMARY KEY,
    first_name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE
);

INSERT INTO students_test (student_id, first_name, email)
VALUES (1, NULL, 'a@example.com');

This fails with a NOT NULL constraint failed error because first_name was declared mandatory. The fix is simply to supply a real value, or, if the field is genuinely sometimes unknown, to remove NOT NULL and handle nulls deliberately in your queries with IS NULL / COALESCE.

Mistake 2: Assuming FOREIGN KEY is always enforced

PRAGMA foreign_keys = ON;

CREATE TABLE departments_fk (
    department_id INTEGER PRIMARY KEY,
    department_name TEXT NOT NULL
);

CREATE TABLE staff_fk (
    staff_id INTEGER PRIMARY KEY,
    staff_name TEXT NOT NULL,
    department_id INTEGER,
    FOREIGN KEY (department_id) REFERENCES departments_fk (department_id)
);

INSERT INTO staff_fk (staff_id, staff_name, department_id) VALUES (1, 'Zoe Park', 99);

This fails with a FOREIGN KEY constraint failed error because department 99 doesn’t exist – but only because PRAGMA foreign_keys = ON; was set first. SQLite (unlike MySQL/PostgreSQL/SQL Server, which enforce foreign keys by default) ships with foreign key enforcement off by default for backward-compatibility reasons; a FOREIGN KEY clause without that pragma is accepted as valid syntax but silently never checked. Always enable it explicitly per connection when working in SQLite.

Mistake 3: Violating UNIQUE with a duplicate value

CREATE TABLE users_test (
    user_id INTEGER PRIMARY KEY,
    username TEXT UNIQUE
);

INSERT INTO users_test (user_id, username) VALUES (1, 'jdoe');
INSERT INTO users_test (user_id, username) VALUES (2, 'jdoe');

The second insert fails with a UNIQUE constraint failed error because 'jdoe' already exists. A common real-world variant of this bug is forgetting that UNIQUE treats multiple NULLs as not equal to each other in most databases (including SQLite), so several rows with a NULL username would not violate this constraint – which surprises people expecting strict duplicate-prevention.

Best Practices

• Add NOT NULL to every column that your application logic actually requires – a missing constraint is a silent invitation for bad data to accumulate.
• Prefer a dedicated surrogate PRIMARY KEY (an auto-incrementing integer id) for most tables, and add a separate UNIQUE constraint for natural keys like email or username.
• Always turn on foreign key enforcement in SQLite (PRAGMA foreign_keys = ON;) – it is off by default and silently ignored otherwise.
• Use CHECK constraints for simple, row-local business rules (ranges, allowed value lists, sign checks) rather than relying solely on application-layer validation.
• Name your constraints (CONSTRAINT chk_gpa CHECK (...)) in production databases so error messages and later schema changes are easier to work with.
• Decide ON DELETE behavior for every foreign key deliberately – CASCADE, SET NULL, or RESTRICT – rather than accepting the default, since the default varies by database and by accident can either block deletes or delete more than you expect.
• Test constraints with intentionally bad data before shipping a schema – if a constraint doesn’t reject what it’s supposed to reject, it isn’t doing its job.

Practice Exercises

• Exercise 1: Create a products table with columns product_id (primary key), product_name (required), price (must be greater than 0), and sku (must be unique). Insert two valid rows, then try an insert that should fail the price check.
• Exercise 2: Create two related tables, authors and books, where books.author_id is a foreign key referencing authors.author_id. Insert one author and two of their books, then write a query joining them.
• Exercise 3: Given the enrollments table from Example 2, write an INSERT that would violate its composite primary key (hint: reuse an existing student_id/course_code pair) and explain in your own words why it fails.

Summary

• NOT NULL, UNIQUE, PRIMARY KEY, FOREIGN KEY, CHECK, and DEFAULT are the six core constraint types.
• Constraints are checked automatically on every write, before anything is committed – a single failed constraint aborts the whole statement.
• PRIMARY KEY is really NOT NULL + UNIQUE plus the role of uniquely identifying a row, and it automatically builds a supporting index.
• Composite constraints (multi-column primary keys, unique constraints, foreign keys) must be declared at the table level, not the column level.
• In SQLite specifically, foreign key enforcement must be turned on per connection with PRAGMA foreign_keys = ON; – it is not enforced by default.
• Well-designed constraints move data validation into the database itself, so bad data is rejected no matter which application or script tries to write it.