---
title: "Enable SQLite ON DELETE CASCADE in C#"
url: https://recursiveai.org/onelite/cookbook/enable-sqlite-foreign-key-cascades/
description: "Enable SQLite foreign keys on a OneLite connection, delete a parent and its children, and see what happens when an insert has no parent."
version: "1.0.0"
---

# Enable SQLite ON DELETE CASCADE in C#

**OneLite** is a free, open-source SQLite-compatible database for .NET from Recursive AI. MIT or Apache-2.0, no native dependencies.

[NuGet](https://www.nuget.org/packages/RecursiveAI.OneLite) · [GitHub](https://github.com/recursive-ai-oss/onelite-csharp) · [Docs](https://recursiveai.org/onelite/docs/) · [Download example (.zip)](https://recursiveai.org/onelite/cookbook/enable-sqlite-foreign-key-cascades/enable-sqlite-foreign-key-cascades.zip)

Turn on `PRAGMA foreign_keys` and declare `ON DELETE CASCADE` to delete a parent’s children with it.

```shell
dotnet add package RecursiveAI.OneLite
```

Requires [.NET 10](https://dotnet.microsoft.com/download/dotnet/10.0) (net10.0); .NET 9 and earlier aren’t supported.

```csharp
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](https://recursiveai.org/onelite/cookbook/enable-sqlite-foreign-key-cascades/enable-sqlite-foreign-key-cascades.zip), then `dotnet run`.

Prints how many children are left after deleting the parent:

```text
Children left: 0
```

## The full program

```csharp
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);
}
```

```text
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`.

```csharp
var enabled = await db.ScalarAsync<long>(
    $"PRAGMA foreign_keys");
Console.WriteLine($"Foreign keys: {enabled}");
```

## Related recipes

- [Create a SQLite database in C#](https://recursiveai.org/onelite/cookbook/create-sqlite-database-csharp/)
- [Use SQLite transactions in C#](https://recursiveai.org/onelite/cookbook/sqlite-transactions-csharp/)

## 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](https://www.sqlite.org/foreignkeys.html#fk_enable). 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](https://recursiveai.org/onelite/cookbook/microsoft-data-sqlite-ef-core-without-native-library/).

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](https://www.sqlite.org/foreignkeys.html#fk_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.
