Turn on PRAGMA foreign_keys and declare ON DELETE CASCADE to delete a parent’s children with it.
dotnet add package RecursiveAI.OneLiteRequires .NET 10 (net10.0); .NET 9 and earlier aren’t supported.
using RecursiveAI.OneLite; await using var db = await Database.OpenAsync(":memory:"); db.ExecuteScript(""" PRAGMA foreign_keys = ON; CREATE TABLE parent( id INTEGER PRIMARY KEY ); CREATE TABLE child( parent_id INTEGER NOT NULL REFERENCES parent(id) ON DELETE CASCADE ); INSERT INTO parent VALUES (1); INSERT INTO child VALUES (1); DELETE FROM parent WHERE id = 1; """); var count = await db.ScalarAsync<long>( $"SELECT count(*) FROM child"); Console.WriteLine($"Children left: {count}");
Unzip the example, then dotnet run.
Prints how many children are left after deleting the parent:
Children left: 0The full program
using RecursiveAI.OneLite; await using var db = await Database.OpenAsync(":memory:"); db.ExecuteScript(""" PRAGMA foreign_keys = ON; CREATE TABLE parent( id INTEGER PRIMARY KEY ); CREATE TABLE child( parent_id INTEGER NOT NULL REFERENCES parent(id) ON DELETE CASCADE ); INSERT INTO parent VALUES (1); INSERT INTO child VALUES (1); DELETE FROM parent WHERE id = 1; """); var count = await db.ScalarAsync<long>( $"SELECT count(*) FROM child"); Console.WriteLine($"Children left: {count}"); try { await db.ExecuteAsync($"INSERT INTO child VALUES (99)"); } catch (OneLiteException ex) when (ex.ExtendedCode == 787) { Console.WriteLine(ex.Message); }
Children left: 0 FOREIGN KEY constraint failed
The program enables enforcement before writing data, then deletes parent 1. It next tries to insert a child for parent 99, which does not exist. This triggers OneLiteException with extended result code 787 (SQLITE_CONSTRAINT_FOREIGNKEY). The setup script uses only fixed SQL; bind parameters on individual statements if values come from input.
Variations
Check enforcement on the current connection. Append this to the snippet at the top; it prints Foreign keys: 1.
var enabled = await db.ScalarAsync<long>( $"PRAGMA foreign_keys"); Console.WriteLine($"Foreign keys: {enabled}");
Related recipes
The short answer
Run PRAGMA foreign_keys = ON on the connection that will perform the delete. Define the child column with REFERENCES parent(id) ON DELETE CASCADE. When you delete a parent, SQLite uses that relationship to delete child rows whose foreign key matches it. The same enforcement rejects inserts of non-null keys that have no parent.
Why is SQLite ON DELETE CASCADE not working?
Check both the connection setting and the table definition. Query PRAGMA foreign_keys on the connection where the delete runs, not on a second connection. If it returns 0, enable it as part of opening the connection, before beginning a transaction. SQLite does not change the setting mid-transaction. Run PRAGMA foreign_keys = ON on every connection, regardless of which library opens it; for Microsoft.Data.Sqlite or EF Core, see the OneLite adapter guide.
If enforcement returns 1, check that ON DELETE CASCADE is part of the child’s foreign key declaration. Turning on enforcement doesn’t turn a plain REFERENCES clause into a cascade: the default delete action is NO ACTION.
What does SQLite ON DELETE CASCADE delete?
It removes the rows that reference the deleted parent through that foreign key. In the example, deleting parent 1 leaves no child rows because the one child belongs to 1. A child of a different parent would be unaffected. ON DELETE CASCADE doesn’t mean ON UPDATE CASCADE: if you change the parent key instead of deleting the row, SQLite uses the separate update action. See SQLite’s foreign key actions.
Decide whether deleting a parent should also remove all the records that belong to it. A cascade fits when those child rows have no independent use. If you want deletion to fail until the children have been dealt with, use an enforcing relationship without a delete cascade. If every child must have a parent, keep NOT NULL, as the example does; otherwise SQLite allows a NULL foreign key without a matching parent.