Keys and Constraints
Try it: Keys and Constraints
How SQLite enforces PRIMARY KEY, UNIQUE, NOT NULL and FOREIGN KEY constraints on INSERT and DELETE, how an INTEGER PRIMARY KEY assigns ids (with and without AUTOINCREMENT), what INSERT OR IGNORE skips, and what ON DELETE RESTRICT, CASCADE and SET NULL do to child rows.
How it works
- INSERT: a NULL id is replaced by max(id) + 1 — or, with AUTOINCREMENT, by one more than the largest id ever used — and last_insert_rowid() reports it.
- Constraints are checked in order: NOT NULL, then the PRIMARY KEY, then UNIQUE, then the FOREIGN KEY (the parent row must exist). The first failure aborts the statement with SQLite's error message.
- INSERT OR IGNORE silently skips a row that breaks NOT NULL, PRIMARY KEY or UNIQUE — but a foreign-key failure is still an error.
- DELETE of a parent row that has children: RESTRICT refuses, CASCADE deletes the children too, SET NULL clears their foreign key. None of this happens unless PRAGMA foreign_keys = ON (SQLite's default is OFF).
Default run (5 steps): 3 artists and 3 albums. PRAGMA foreign_keys = ON, ON DELETE RESTRICT. Choose an operation. Example: INSERT INTO artist (name) VALUES ('Queen'). … Error: UNIQUE constraint failed: artist.name. Nothing was changed (the statement is rolled back).
Simplified: A toy relational engine in the browser, not SQLite itself: a small structured query model built from the controls (no SQL text is parsed or executed), typed INTEGER/TEXT/NULL values, and tables of at most a few rows. Results were checked against Python's sqlite3 (SQLite 3.49). Two fixed tables of at most 8 rows and ids up to 99; changing the schema recreates the tables with the starting rows. Error texts and ids were checked step by step against sqlite3.
Loading the simulation…