Understanding SQLite Constraints: Ensuring Data Integrity in Your Database

SQLite constraints are the rulebook your database enforces automatically. Every time a row is inserted or updated, SQLite checks it against the constraints you defined — rejecting bad data before it can corrupt your application. In this guide, updated for SQLite 3.51 (released November 2025), you will learn every constraint type, how conflict resolution works, when to use STRICT tables, and the best practices professionals follow to keep data clean.
Whether you are building a mobile app, an embedded system, or a Python project with a local database, constraints are the cheapest insurance you can buy against data corruption. Let us go through them one by one, with syntax you can copy and real behavior you can rely on.
Table of Contents
What are SQLite constraints?
A constraint is a rule attached to a column or a table that limits what data can be stored. Instead of trusting every application, script, or import job to validate data correctly, you declare the rules once in the schema — and SQLite enforces them on every INSERT and UPDATE, forever.
Constraints protect three kinds of integrity. Entity integrity means every row is uniquely identifiable (PRIMARY KEY). Domain integrity means values are valid for their column — no text in a date field, no negative prices (NOT NULL, CHECK). Referential integrity means relationships between tables stay consistent — no order pointing to a customer that does not exist (FOREIGN KEY).
SQLite constraint types at a glance
| Constraint | What it enforces | Column level | Table level |
|---|---|---|---|
| PRIMARY KEY | Unique, non-null identifier for each row | Yes | Yes |
| NOT NULL | Column must always have a value | Yes | No |
| UNIQUE | No two rows may share the same value | Yes | Yes |
| CHECK | Values must satisfy a custom expression | Yes | Yes |
| FOREIGN KEY | Values must exist in another table’s key | Yes | Yes |
| DEFAULT | Fallback value when none is supplied | Yes | No |
PRIMARY KEY: one identifier per row
Each SQLite table may have at most one PRIMARY KEY. It guarantees that every row has a unique combination of values in the key columns — the foundation of entity integrity. Declare it on a single column or as a table-level constraint over several columns (a composite key).
The INTEGER PRIMARY KEY rowid shortcut
SQLite has a famous optimization: if a rowid table has a single-column primary key declared as INTEGER, that column becomes an alias for the internal rowid. Lookups are faster, and inserting NULL auto-assigns the next available integer. This is why id INTEGER PRIMARY KEY is the idiomatic primary key in SQLite.
One quirk to know: per the SQL standard, PRIMARY KEY should imply NOT NULL, but due to a historical bug SQLite allows NULLs in PRIMARY KEY columns — except for INTEGER PRIMARY KEY columns, WITHOUT ROWID tables, STRICT tables, or columns explicitly declared NOT NULL. When in doubt, add NOT NULL yourself.
Composite primary keys
When no single column identifies a row — for example, a junction table linking students to courses — define the key at table level:
CREATE TABLE enrollment(
student_id INTEGER,
course_id INTEGER,
enrolled_on TEXT DEFAULT CURRENT_DATE,
PRIMARY KEY(student_id, course_id)
);
NOT NULL: no missing values allowed
NOT NULL is the simplest constraint: the column must contain a value in every row. It can only be attached to a column definition, never declared at table level. Use it for every column your application logic depends on — names, emails, prices, timestamps.
CREATE TABLE customer(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE
);
UNIQUE: no duplicate values
A UNIQUE constraint works like PRIMARY KEY except a table can have many of them, and (in SQLite) UNIQUE columns may contain NULLs. SQLite implements UNIQUE and PRIMARY KEY constraints with unique indexes behind the scenes. Use UNIQUE for natural identifiers like emails, usernames, and SKU codes.
For multi-column uniqueness — say, a seat number that must be unique per flight — use a table-level UNIQUE constraint:
CREATE TABLE booking(
id INTEGER PRIMARY KEY,
flight_no TEXT NOT NULL,
seat TEXT NOT NULL,
UNIQUE(flight_no, seat)
);
CHECK: your own validation rules
CHECK constraints let you write any boolean expression a value must satisfy — ranges, formats, cross-column logic. The expression is evaluated on every write; if it returns false (zero), the write is rejected.
CREATE TABLE product(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
price REAL CHECK(price > 0),
stock INTEGER CHECK(stock >= 0),
CHECK(price < 1000000)
);
Two things to remember about CHECK. First, it is only verified when the table is written, not when it is read — and enforcement can be disabled with PRAGMA ignore_check_constraints=ON, so a corrupted file could theoretically contain violating rows. Second, CHECK cannot reference other tables; for cross-table rules, use triggers or FOREIGN KEY constraints.
FOREIGN KEY: relationships you can trust
A FOREIGN KEY guarantees that a value in the child table matches an existing key in the parent table — no orphaned orders, no reviews for products that were deleted. The parent key must be the parent’s PRIMARY KEY or a UNIQUE column.
Foreign keys are off by default — enable them
This catches almost every beginner: SQLite parses foreign key definitions but does not enforce them unless you opt in, for backwards compatibility. Run this on every database connection:
PRAGMA foreign_keys = ON;
Verify it took effect with PRAGMA foreign_keys; (it returns 1). Libraries and ORMs often expose this as a connection option — enable it once in your connection setup and never think about it again.
ON DELETE and ON UPDATE actions
Foreign keys can react automatically when a parent row changes. The five actions are NO ACTION (reject the change if children exist), RESTRICT (reject immediately), CASCADE (propagate the delete or update to children), SET NULL (orphan the children by nulling their key), and SET DEFAULT (reset children to their default key).
CREATE TABLE orders(
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL
REFERENCES customer(id)
ON DELETE CASCADE
ON UPDATE CASCADE
);
Deferred foreign keys
Normally a foreign key is checked after each statement (immediate). Declaring it DEFERRABLE INITIALLY DEFERRED postpones the check until COMMIT — useful when a transaction temporarily breaks a relationship mid-way, such as swapping two rows’ keys. For performance, also index your child key columns: every parent delete or update runs a lookup against them.
DEFAULT: sensible fallback values
DEFAULT is not strictly a constraint, but it belongs in the integrity toolkit: it supplies a value when an INSERT omits the column, so NOT NULL columns stay valid without application code. Defaults can be constants, expressions in parentheses, or CURRENT_TIME, CURRENT_DATE, and CURRENT_TIMESTAMP.
CREATE TABLE event_log(
id INTEGER PRIMARY KEY,
message TEXT NOT NULL,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP,
retries INTEGER NOT NULL DEFAULT 0
);
Column-level vs table-level constraints
Constraints declared inside a column definition apply to that column only. Constraints declared after the column list apply to the table and can span multiple columns. Choosing correctly matters:
| Aspect | Column-level | Table-level |
|---|---|---|
| Scope | Single column | One or more columns |
| Composite keys | Not possible | Required for multi-column keys |
| NOT NULL | Allowed | Not allowed |
| DEFAULT | Allowed | Not allowed |
| Naming the constraint | Optional | Recommended for clear error messages |
Name your table-level constraints with CONSTRAINT name ... — when a violation occurs, the error message names the constraint, and a meaningful name like chk_positive_price turns a cryptic failure into a five-second diagnosis.
What happens on a violation: conflict resolution
When a write breaks a PRIMARY KEY, UNIQUE, NOT NULL, or CHECK constraint, SQLite follows a conflict resolution algorithm. The default is ABORT, which rolls back just the offending statement. You can override it per constraint with an ON CONFLICT clause:
| Algorithm | Behavior on violation | Typical use |
|---|---|---|
| ROLLBACK | Aborts the statement and rolls back the whole transaction | All-or-nothing batch imports |
| ABORT (default) | Aborts the statement; earlier statements in the transaction survive | Normal application writes |
| FAIL | Stops at the first violation but keeps prior successful rows | Bulk loads where partial progress is fine |
| IGNORE | Skips the violating row and continues silently | Idempotent sync jobs |
| REPLACE | Deletes the conflicting row, then inserts the new one | Upsert-style synchronization |
Example: email TEXT UNIQUE ON CONFLICT REPLACE turns a duplicate-email insert into an automatic update of the existing row. Use REPLACE carefully — it deletes before inserting, which can fire DELETE triggers and cascade.
STRICT tables and SQLite’s 2025 releases
SQLite is famously flexible about types — a TEXT column will happily store an integer. STRICT tables (available since 3.37.0) opt into rigid type checking: each column accepts only its declared type, catching a whole class of application bugs at the database boundary. Declare one with:
CREATE TABLE sensor_reading(
id INTEGER PRIMARY KEY,
device TEXT NOT NULL,
value REAL NOT NULL,
taken_at TEXT NOT NULL
) STRICT;
SQLite celebrated its 25th anniversary with version 3.50.0 (May 29, 2025), followed by patch releases through 3.50.4. Then version 3.51.0 arrived on November 4, 2025 with two new JSON functions (jsonb_each and jsonb_tree), CLI and performance enhancements — and notably, it began enforcing STRICT typing on computed columns too. The direction is clear: SQLite keeps its famous flexibility by default, but gives you progressively stronger tools to lock data down. If you are starting a schema today, evaluate STRICT tables seriously.
Practical examples: putting it all together
Here is a small e-commerce schema that uses every major constraint type in realistic ways:
PRAGMA foreign_keys = ON;
CREATE TABLE customer(
id INTEGER PRIMARY KEY,
name TEXT NOT NULL,
email TEXT NOT NULL UNIQUE,
created_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;
CREATE TABLE product(
id INTEGER PRIMARY KEY,
sku TEXT NOT NULL UNIQUE,
name TEXT NOT NULL,
price REAL NOT NULL CHECK(price > 0),
stock INTEGER NOT NULL DEFAULT 0 CHECK(stock >= 0)
) STRICT;
CREATE TABLE orders(
id INTEGER PRIMARY KEY,
customer_id INTEGER NOT NULL REFERENCES customer(id)
ON DELETE RESTRICT ON UPDATE CASCADE,
placed_at TEXT NOT NULL DEFAULT CURRENT_TIMESTAMP
) STRICT;
CREATE TABLE order_item(
order_id INTEGER NOT NULL REFERENCES orders(id)
ON DELETE CASCADE,
product_id INTEGER NOT NULL REFERENCES product(id),
qty INTEGER NOT NULL CHECK(qty > 0),
PRIMARY KEY(order_id, product_id)
) STRICT;
Notice the design decisions: RESTRICT on customer deletion (you cannot delete a customer with orders — archive them instead), CASCADE on order items (deleting an order removes its lines), CHECK on prices and quantities, and STRICT tables throughout for type safety.
Best practices for SQLite constraints
- Enable foreign keys on every connection.
PRAGMA foreign_keys = ON;belongs in your connection setup, not in your memory. - Use INTEGER PRIMARY KEY for row identifiers. You get the rowid alias optimization for free.
- Add NOT NULL aggressively. Every nullable column is a future bug report. Pair it with DEFAULT so inserts stay convenient.
- Prefer CHECK over application validation for rules that must never be broken — prices, quantities, status values. Application code changes; the schema endures.
- Name table-level constraints (
CONSTRAINT chk_positive_price CHECK(price > 0)) so error messages diagnose themselves. - Index foreign key child columns. Parent deletes and updates probe the child table; without an index, every delete is a full scan.
- Evaluate STRICT tables for new schemas. They catch type bugs at the boundary instead of in production.
- Keep learning deliberately. SQLite pairs naturally with Python and app development — explore the best programming languages to learn in 2026, or build skills systematically with job-focused computer courses.
Troubleshooting common constraint errors
- “FOREIGN KEY constraint failed” on a schema that looks correct — you forgot
PRAGMA foreign_keys = ON. This is the number one SQLite gotcha. - “UNIQUE constraint failed: t.email” — a duplicate value was inserted. Decide whether the application should update the existing row (ON CONFLICT REPLACE) or reject the write.
- “NOT NULL constraint failed” — the INSERT omitted a column with no default. Add a DEFAULT or supply the value.
- “CHECK constraint failed” — the value broke a CHECK expression. The error names the table, not the constraint, unless you named it — another reason to name constraints.
- “foreign key mismatch” — the parent key is not a PRIMARY KEY or UNIQUE column, or the parent table does not exist. Check the parent schema first.
- Constraint works in testing but not in production — verify the production SQLite version (
SELECT sqlite_version();) and thatPRAGMA foreign_keys;returns 1 on the production connection.
Debugging other developers’ schemas is a marketable skill: database reliability expertise features among the highest paying careers in 2026, and constraint design is a favorite interview topic.
Frequently asked questions
What are the main constraints in SQLite?
SQLite supports PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY, and DEFAULT. PRIMARY KEY uniquely identifies each row, NOT NULL bans missing values, UNIQUE bans duplicates, CHECK enforces custom expressions, FOREIGN KEY enforces relationships between tables, and DEFAULT supplies fallback values.
Why is my SQLite foreign key not working?
Because foreign key enforcement is disabled by default in SQLite for backwards compatibility. You must run PRAGMA foreign_keys = ON on every database connection. Verify with PRAGMA foreign_keys, which should return 1.
What is the difference between UNIQUE and PRIMARY KEY in SQLite?
A table can have only one PRIMARY KEY but many UNIQUE constraints. Both are implemented with unique indexes. INTEGER PRIMARY KEY has a special optimization: it becomes an alias for the internal rowid. UNIQUE columns may contain NULL values, while primary keys should be NOT NULL.
When are SQLite CHECK constraints verified?
CHECK constraints are verified only when the table is written (INSERT or UPDATE), not when it is read. Enforcement can also be disabled with PRAGMA ignore_check_constraints=ON. Design your application accordingly and do not rely on CHECK for read-time guarantees.
What does ON CONFLICT REPLACE do in SQLite?
ON CONFLICT REPLACE changes the violation response: instead of aborting, SQLite deletes the conflicting row and inserts the new one. It is useful for upsert-style synchronization, but use it carefully since the delete can fire triggers and cascade to child tables.
Should I use STRICT tables in SQLite?
For new schemas, usually yes. STRICT tables enforce declared column types, catching type bugs at the database boundary. Since SQLite 3.51 (November 2025), STRICT typing is also enforced on computed columns. Legacy schemas may need migration work before they can go STRICT.
Research citations
- SQLite.org, “CREATE TABLE” — official documentation of column definitions, constraints, STRICT tables, and conflict resolution: sqlite.org/lang_createtable.html
- SQLite.org, “SQLite Foreign Key Support” — enabling foreign keys, ON DELETE/UPDATE actions, deferred constraints: sqlite.org/foreignkeys.html
- SQLite.org, “SQLite Release 3.51.0 On 2025-11-04” — release notes including STRICT typing enforcement on computed columns: sqlite.org/releaselog/3_51_0.html
Key takeaways
- Constraints (PRIMARY KEY, NOT NULL, UNIQUE, CHECK, FOREIGN KEY) are enforced by SQLite on every INSERT and UPDATE — declare them once, benefit forever.
- Foreign key enforcement is disabled by default — run
PRAGMA foreign_keys = ONon every connection. - Use ON CONFLICT clauses to control violation behavior, from ABORT to REPLACE.
- STRICT tables add real type safety, and SQLite 3.51 (November 2025) extends STRICT typing to computed columns.
- Name your constraints, index foreign key columns, and validate with
PRAGMA integrity_check.
Good constraints make bad data impossible instead of merely unlikely. Design your schema defensively, and your future self — debugging at 2 a.m. — will thank you.



