TopolearnForeign keys are off by default in SQLite, so many apps believe they're protected when they aren't.
SQLite parses REFERENCES clauses but, for backward compatibility, doesn't enforce foreign keys unless you enable them on each connection: PRAGMA foreign_keys = ON. The setting isn't stored in the database file; every new connection starts with it off. It also can't be changed inside a transaction.
PRAGMA statements read or change SQLite settings and inspect the database. The ones worth knowing:
PRAGMA foreign_keys = ON;— enforce references (per connection).PRAGMA journal_mode = WAL;— better concurrency; stored in the file (later lesson).PRAGMA busy_timeout = 5000;— wait up to 5 s for locks instead of failing at once.PRAGMA synchronous = NORMAL;— a common speed/durability trade-off with WAL.PRAGMA foreign_key_check;— list existing rows that violate foreign keys (useful after enabling them on an old database).PRAGMA integrity_check;— verify the file's structure.PRAGMA table_info(t);/PRAGMA index_list(t);— schema introspection, also available as table-valued functions:SELECT * FROM pragma_table_info('t').PRAGMA user_version;— an integer you can use for schema versioning.
Put the per-connection PRAGMAs in one function that opens connections, so no code path forgets them.
Example
import sqlite3
def connect(path):
con = sqlite3.connect(path)
con.execute("PRAGMA foreign_keys = ON")
con.execute("PRAGMA busy_timeout = 5000")
return con
con = connect(":memory:")
con.executescript('''
CREATE TABLE author (id INTEGER PRIMARY KEY, name TEXT);
CREATE TABLE book (id INTEGER PRIMARY KEY,
author_id INTEGER NOT NULL REFERENCES author(id) ON DELETE CASCADE);
INSERT INTO author VALUES (1, 'Toumanian');
INSERT INTO book VALUES (10, 1);
''')
try:
con.execute("INSERT INTO book VALUES (11, 99)")
except sqlite3.IntegrityError as e:
print(e) # FOREIGN KEY constraint failed
con.execute("DELETE FROM author WHERE id = 1")
print(con.execute("SELECT count(*) FROM book").fetchone()) # (0,) cascaded
print(con.execute("SELECT name, type FROM pragma_table_info('book')").fetchall())
# [('id', 'INTEGER'), ('author_id', 'INTEGER')]Remember
- Foreign keys are off by default: PRAGMA foreign_keys = ON on every connection
- PRAGMAs configure the connection or inspect the database
- Centralise connection setup so every connection gets the same PRAGMAs
Watch out for
- Enabling foreign keys once in the shell and assuming the application's connections enforce them too.