👀 Views: 39 💬 Answers: 1 📅 Created: 2025-06-14

sqlite foreign-keys wal C

I'm currently working on a project using SQLite 3.38.0 with WAL mode enabled, and I'm working with an scenario where foreign key constraints seem to not be enforced during certain transactions. I've defined foreign keys in my schema like this: ```sql CREATE TABLE parent ( id INTEGER PRIMARY KEY, name TEXT NOT NULL ); CREATE TABLE child ( id INTEGER PRIMARY KEY, parent_id INTEGER, FOREIGN KEY (parent_id) REFERENCES parent(id) ON DELETE CASCADE ); ``` When inserting data, I initially thought that foreign key constraints would prevent me from inserting a `child` record that references a non-existent `parent_id`. Instead, I get the following behavior: ``` behavior: FOREIGN KEY constraint failed ``` However, this behavior seems to only occur when I try to insert into the `child` table *after* inserting a `parent` without committing the transaction first. In WAL mode, it appears that the constraints are not checked until the transaction is committed. I've tried explicitly enabling foreign key constraints with: ```sql PRAGMA foreign_keys = ON; ``` And I've ensured that the constraints are correctly defined. Yet, when I roll back the transaction and retry, I still face the same question. Is there a known scenario with WAL mode and how it interacts with foreign key constraints, or is there something I'm missing in my transaction handling? I'm using SQLite's native C API to manage these transactions and my insert statements look like this: ```c sqlite3_exec(db, "INSERT INTO parent (name) VALUES ('Parent1');", 0, 0, &errMsg); sqlite3_exec(db, "BEGIN;", 0, 0, &errMsg); sqlite3_exec(db, "INSERT INTO child (parent_id) VALUES (999);", 0, 0, &errMsg); sqlite3_exec(db, "COMMIT;", 0, 0, &errMsg); ``` Any insights or workarounds would be greatly appreciated!