# How Foreign Keys Are Enforced in ai-memory's SQLite Database

> Discover how ai-memory enforces foreign key constraints in SQLite using PRAGMA foreign_keys. Learn about its strategic disabling during migrations and re-enabling for robust data integrity.

- Repository: [Fabio Akita/ai-memory](https://github.com/akitaonrails/ai-memory)
- Tags: how-to-guide
- Published: 2026-08-22

---

**ai-memory enforces foreign key constraints by toggling SQLite's `PRAGMA foreign_keys` pragma—disabling it only during schema migrations in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs) and [`migrations.rs`](https://github.com/akitaonrails/ai-memory/blob/main/migrations.rs), then re-enabling it for all runtime operations to ensure referential integrity across the single-writer thread and read-only connection pools.**

ai-memory is a Rust-based knowledge management system that persists all data to a single SQLite file (`data/db/memory.sqlite`). To maintain strict referential integrity while allowing flexible schema evolution, the codebase implements a precise foreign key management strategy. This approach ensures that **foreign key constraints** are validated for every production transaction while safely bypassing them only during controlled migration procedures.

## The Foreign Key Toggle Strategy

The core enforcement mechanism relies on SQLite's `foreign_keys` pragma. Rather than leaving this setting to chance, ai-memory explicitly manages the pragma lifecycle at every database initialization, ensuring constraints are temporarily lifted for schema modifications and then restored for normal operations.

### Disabling Constraints During Store Initialization

In [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs), the `Store::open` method orchestrates the foreign key state during database startup. The implementation first disables foreign keys to allow the migration runner to rebuild tables without interference, executes all pending migrations, then re-enables constraints before handing connections to the application.

```rust
// In crates/ai-memory-store/src/lib.rs
conn.pragma_update(None, "foreign_keys", "OFF")?;   // disable FK while migrations run
migrations::run(&mut conn)?;
conn.pragma_update(None, "foreign_keys", "ON")?;    // enable FK for runtime operations

```

This pattern guarantees that schema changes—such as creating intermediate tables or dropping deprecated columns—can proceed without being blocked by existing referential constraints.

### Migration Script Handling

The migration runner itself, located in [`crates/ai-memory-store/src/migrations.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/migrations.rs), applies the same toggle pattern around individual migration steps that require temporary constraint relaxation. When a migration must recreate a table or perform data transformations that would violate foreign keys transiently, the code explicitly manages the pragma state.

```rust
conn.pragma_update(None, "foreign_keys", "OFF")?;
// …run statements that would violate FK temporarily…
conn.pragma_update(None, "foreign_keys", "ON")?;

```

This granular control ensures that migrations remain atomic and safe, with constraints restored immediately after each risky operation.

## Runtime Enforcement and Connection Validation

Once the store initialization completes, every connection used by the application guarantees foreign key validation. The **single-writer** thread and the **read-only connection pool** both inherit connections where `foreign_keys = ON` has been explicitly set, ensuring that every `INSERT`, `UPDATE`, and `DELETE` undergoes SQLite's native referential integrity checks.

The test suite further validates this behavior by explicitly checking for violations after migration failures:

```rust
let foreign_key_errors: i64 = conn
    .query_row("SELECT COUNT(*) FROM pragma_foreign_key_check", [], |r| r.get(0))
    .unwrap();
assert_eq!(foreign_key_errors, 0);

```

This query against `pragma_foreign_key_check` ensures that no orphaned records or constraint violations persist after schema modifications.

## Scope-Guarding Triggers for Complex Constraints

Beyond SQLite's built-in foreign key enforcement, ai-memory implements additional data integrity rules through database triggers. These triggers enforce cross-entity invariants—such as requiring matching workspace and project IDs across tables—that go beyond standard referential constraints.

Key triggers defined in the migration scripts include:

- `sessions_ws_proj_pairing_ai` – Validates that session records reference consistent workspace and project pairs.
- `entity_page_links_scope_pairing_ai` – Ensures that entity links maintain proper scope alignment with their parent pages.

These triggers execute whenever foreign keys are enabled, providing an additional layer of validation for the application's complex relational model.

## Practical Examples

### Verifying Foreign Key Status

To confirm that foreign keys are enabled on any given connection, query the pragma directly:

```rust
use ai_memory_store::Store;
use std::path::PathBuf;

let data_dir = PathBuf::from("/path/to/ai-memory/data");
let store = Store::open(&data_dir).expect("failed to open store");

// Verify FK is on (should return 1):
let conn = store.writer.handle().connection();
let fk_status: i32 = conn.query_row(
    "PRAGMA foreign_keys",
    [],
    |row| row.get(0)
).unwrap();

assert_eq!(fk_status, 1);

```

### Handling Constraint Violations

When foreign keys are enabled, attempting to insert orphaned records results in immediate errors:

```rust
let err = conn.execute(
    "INSERT INTO pages (id, workspace_id, project_id, path) VALUES (?, ?, ?, ?)",
    params![uuid, b"nonexistent_ws", b"nonexistent_proj", "orphan.md"]
).unwrap_err();

assert!(err.to_string().contains("foreign key constraint failed"));

```

### Temporarily Disabling Foreign Keys in Custom Migrations

When writing custom migration logic that requires temporarily relaxing constraints, follow the established pattern from [`crates/ai-memory-store/src/ops.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/ops.rs):

```rust
conn.pragma_update(None, "foreign_keys", "OFF")?;
conn.execute(
    "CREATE TABLE workstreams_tmp AS SELECT * FROM workstreams WHERE created_at > ?",
    [timestamp]
)?;
// Perform data migration...
conn.pragma_update(None, "foreign_keys", "ON")?;

```

## Summary

- **Foreign keys are disabled** via `conn.pragma_update(None, "foreign_keys", "OFF")` only during schema migration execution in `Store::open` and the migration runner.
- **Constraints are re-enabled** immediately after migrations complete using `conn.pragma_update(None, "foreign_keys", "ON")`, ensuring all runtime connections validate referential integrity.
- **Connection pools** inherit the enabled state, applying foreign key checks to every operation performed by the single-writer thread and read-only workers.
- **Validation queries** against `pragma_foreign_key_check` verify zero violations exist after migration failures.
- **Custom triggers** such as `sessions_ws_proj_pairing_ai` enforce higher-level scope constraints beyond standard foreign key relationships.

## Frequently Asked Questions

### Why does ai-memory disable foreign keys during migrations?

Schema migrations often require intermediate steps—such as creating temporary tables or dropping columns—that would violate referential integrity constraints. By disabling `PRAGMA foreign_keys` only during these controlled procedures, ai-memory allows safe schema evolution while ensuring constraints are active for all production data operations.

### How can I verify that foreign keys are enabled in a connection?

Execute the query `PRAGMA foreign_keys` against the connection. According to the implementation in [`crates/ai-memory-store/src/lib.rs`](https://github.com/akitaonrails/ai-memory/blob/main/crates/ai-memory-store/src/lib.rs), this returns `1` when enforcement is active and `0` when disabled. The `Store::open` method guarantees this pragma is set to `ON` before returning the store handle.

### What happens if a migration introduces a foreign key violation?

The migration runner explicitly checks for violations after executing migration steps. If `SELECT COUNT(*) FROM pragma_foreign_key_check` returns a non-zero value, the test suite will fail, indicating that the migration left the database in an inconsistent state with orphaned records or invalid references.

### How does ai-memory enforce constraints beyond standard foreign keys?

The system defines SQLite triggers such as `entity_page_links_scope_pairing_ai` and `sessions_ws_proj_pairing_ai` directly in the migration scripts. These triggers fire during data modification operations to enforce cross-entity business rules—such as matching workspace/project scope—that standard foreign key constraints cannot express alone.