SQLite / Basic: the embedded engine and correct data / Lesson 3

Keys, constraints, and foreign-key enforcement

Model stable identity and relationships with SQLite-specific key rules, explicit nullability, checks, and verified connection-level foreign-key enforcement.

About 5 min readContent reviewed 2026-10-25

On this page

Lesson goals

Separate row identity from business uniqueness.
Enable and verify foreign-key enforcement.
Explain constraint failures as contract evidence.

Choose keys from the entity definition

A primary key identifies a row, while UNIQUE can protect another business identifier. A customer may have an internal integer identifier and a unique external account code; those are different contracts. In a rowid table, the exact declaration INTEGER PRIMARY KEY aliases the rowid, with documented exceptions such as descending primary-key syntax. INT PRIMARY KEY is not the same alias declaration.

Do not generalize another engine's auto-number syntax into SQLite. An omitted INTEGER PRIMARY KEY can receive an automatically selected rowid without AUTOINCREMENT. AUTOINCREMENT has additional bookkeeping and a narrower no-reuse purpose; it is not required for ordinary identity and does not promise gap-free business invoice numbering. Stable identifiers and a separate numbering policy are easier to reason about.

Protect required fields and local invariants

NOT NULL rejects missing values, CHECK rejects false conditions, and UNIQUE prevents repeated key values under its comparison rules. CHECK does not reject a NULL result as if it were false, so combine required quantity checks with explicit NOT NULL. Ordinary SQLite primary-key nullability has historical quirks; declaring requirements explicitly or using appropriate STRICT/WITHOUT ROWID designs avoids accidental assumptions.

The first script protects a required code and positive quantity. The valid row shows the intended state rather than deliberately failing halfway through setup. A real caller should distinguish a constraint rejection from successful insertion and must not report durable success before its transaction completes. Name important constraints or map stable error information to domain responses without exposing internal details.

sql A required unique code and positive quantity

CREATE TABLE product(id INTEGER PRIMARY KEY, code TEXT NOT NULL UNIQUE,
 quantity INTEGER NOT NULL CHECK(quantity>0)) STRICT;
INSERT INTO product(code,quantity) VALUES('P-01',3);
SELECT id,code,quantity FROM product ORDER BY id;

Expected output

id|code|quantity
1|P-01|3

The omitted integer primary key receives1 in this fresh table, while constraints preserve the declared row invariant.

Enable enforcement on each connection before work

A FOREIGN KEY declaration describes a relationship, but enforcement must be enabled and verified for the actual connection using PRAGMA foreign_keys. Do not assume an application framework, another connection, or a shell enabled it for this one. Changing this setting while a transaction is active does not take effect as an ordinary mid-transaction switch. Set connection policy before beginning related work.

The second script enables enforcement before creating and using its training tables. An order references customer1, and the join confirms that relationship. The parent key must meet SQLite's uniqueness and collation requirements. Index child-key columns when justified for efficient parent changes; a foreign-key declaration does not automatically create every useful child-side index.

sql An enforced parent-child relationship

PRAGMA foreign_keys=ON;
CREATE TABLE customer(id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE purchase(id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL
 REFERENCES customer(id));
INSERT INTO customer VALUES(1,'Amina');
INSERT INTO purchase VALUES(10,1);
SELECT p.id,c.name FROM purchase AS p JOIN customer AS c
 ON c.id=p.customer_id ORDER BY p.id;
PRAGMA foreign_keys;

Expected output

id|name
10|Amina

foreign_keys
1

The order joins to Amina, and the final pragma reports1 for this connection. The script never relies on an external schema.

Decide deletion and optionality deliberately

A nullable child key can represent an optional relationship; required relationships also need NOT NULL. ON DELETE actions such as RESTRICT, SET NULL, or CASCADE express very different policies. Cascading deletion is not merely a convenient way to silence an error: it can remove substantial dependent data. Review retention and audit requirements before choosing it.

Deferred foreign keys can allow a temporary inconsistency inside a transaction that must be resolved before the required check point. This is not permission to commit an orphan. PRAGMA foreign_key_check can inspect violations, while integrity_check has a different scope and is not a substitute for that relational check. A successful schema creation does not prove existing data satisfies every intended relationship.

Test both accepted and rejected transitions

For each constraint, create a small test that should succeed and one that should fail without leaving partial business state. Include duplicate business codes, missing required values, nonpositive quantities, missing parents, and parent deletion with children. Error handling belongs to the transaction owner, which must decide whether to roll back and how to report a rejected request.

Keep schema rules and application validation consistent. UI checks provide helpful feedback, while database constraints protect imports and alternative writers too. Do not disable foreign keys or remove a UNIQUE rule simply because a current dataset violates it. Investigate the source, repair it through a reviewed process, and verify the invariant before widening access to the database.

Common errors and fixes

FOREIGN KEY constraint failed

Why it happens: A required parent is missing or a parent change violates the relationship.
How to fix it: Verify parent/child keys and apply the intended transaction and deletion policy.
Prevent it next time: Enable enforcement before work and test missing parents.

UNIQUE constraint failed

Why it happens: A business or primary key repeats an existing value.
How to fix it: Reject or reconcile the duplicate under an explicit request policy.
Prevent it next time: Test duplicate submissions and key normalization.

A CHECK accepted a missing quantity.

Why it happens: NULL evaluation was mistaken for a false predicate.
How to fix it: Add NOT NULL for the required field.
Prevent it next time: Test missing, zero, and negative separately.

Check your understanding

1. Does creating REFERENCES customer(id) prove every application connection enforces it?

Show worked solution

No. Enable and query PRAGMA foreign_keys on each relevant connection before transactions. Test an invalid parent reference in an isolated database. A declaration and an active enforcement policy are separate facts.

2. Why is AUTOINCREMENT not a gap-free invoice-number solution?