SQLite Foreign Keys: Verify Enforcement on Every Connection

Declaring a foreign key in a SQLite schema does not by itself prove that every application connection enforces it. Configure enforcement deliberately when opening each connection, verify the setting, and test a violating write. Then check whether older data already contains orphaned references.

The SQLite foreign-key documentation explains runtime enablement, build support, and transaction limitations. Do not rely on a remembered default: your application should establish and verify the behavior it requires.

Set the policy when opening a connection

For a build supporting foreign keys, enable the feature before beginning application transactions:

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

The readback should return the enabled setting. If it does not, stop initialization rather than continuing with a warning that nobody reads. The documentation also describes builds where support is omitted; a missing or unexpected result requires investigation.

Apply this setup to every new connection, including background jobs, migrations, tests, and administrative scripts. Enabling it in a command-line session does not configure the connections used by a running web application.

Do not toggle it inside the transaction

SQLite documents that changing this setting during a multi-statement transaction does not take effect as an ordinary mid-transaction switch. A migration wrapper that starts a transaction before running initialization can therefore undermine an apparently correct setup statement.

Inspect the actual connection lifecycle in your database library. Some frameworks begin transactions implicitly. Put the connection policy at the point where the connection is created and verify it before the first operation that depends on enforcement.

If a migration genuinely requires a different policy, design that process explicitly with backups, validation, and the library’s documented transaction behavior. Do not casually disable constraints around a production import and assume switching them back on repairs every row.

Prove enforcement with a tiny isolated test

Use a disposable test database, not an existing customer database:

PRAGMA foreign_keys = ON;
CREATE TABLE account (
  id INTEGER PRIMARY KEY
);
CREATE TABLE note (
  id INTEGER PRIMARY KEY,
  account_id INTEGER NOT NULL REFERENCES account(id)
);
INSERT INTO account(id) VALUES (1);
INSERT INTO note(id, account_id) VALUES (10, 1);

The valid insert should succeed. Next, in a test that expects a constraint failure, attempt a note referencing account 999. Assert that the write is rejected and that the test harness does not silently swallow the error.

Repeat the test after opening a second connection. This is the step that catches setup applied only to one convenient connection rather than to the application’s complete connection factory.

Check data that predates enforcement

Turning enforcement on is not the same as auditing historical data. Run the documented integrity checks appropriate to the database, including PRAGMA foreign_key_check, and inspect the reported violations before calling the migration complete.

For a human-readable diagnosis, an explicit orphan query can also help:

SELECT note.id, note.account_id
FROM note
LEFT JOIN account ON account.id = note.account_id
WHERE account.id IS NULL;

This query fits the simple non-null schema above. Composite keys, nullable references, and more complex relationships require corresponding logic. Do not reuse the query blindly for every schema.

Choose a repair according to business meaning. A missing parent might require restoring a legitimate record, correcting an identifier, or quarantining an invalid child. Deleting every reported row can destroy useful data and is not an automatic repair strategy.

Review parent keys, actions and indexes

The referenced key must satisfy SQLite’s documented parent-key requirements. A column that looks unique in current data is not a substitute for the required schema constraint and compatible definition.

Choose deletion and update actions deliberately. A cascade can be correct for dependent rows but dangerous for records that should remain as an audit trail. Test the intended parent change and compare all affected child rows.

Consider an index on child-key columns for realistic workloads. The documentation explains why parent changes may otherwise need expensive child searches. Correctness and performance are separate checks: an enforced relationship can still produce a slow deletion path on a large table.

Make connection policy part of regression testing

Add a startup assertion or health diagnostic that reports the effective setting without exposing data. Test pooled connections after recycling and test maintenance scripts under the same policy as the main application.

When upgrading a driver or changing transaction wrappers, rerun the isolated constraint test. A successful schema migration does not prove that future application connections use the same configuration.

Test parent deletion as well as child insertion. A system may reject new orphans correctly while a wrongly selected cascade removes more data than intended. Assert both the expected rejection or cascade and the preservation of unrelated rows.

Conclusion

Treat foreign-key enforcement as a verified connection policy, not just schema text. Enable it before transactions, test a rejected write on multiple connections, and audit historical relationships before trusting the database invariant.