SQLite Foreign Keys Belong to the Connection

A small AI app can store conversations in one table and messages in another. Each message can carry a conversation ID declared as a foreign key. With enforcement enabled, SQLite rejects a message that refers to a missing conversation. It can also reject deletion of a conversation that still has dependent messages. SQLite permits a child row with a NULL key, so use NOT NULL when every message must have a conversation. SQLite’s foreign-key guide explains these rules.

The setting lives on each connection

The declaration in the table schema is only part of the setup. SQLite says applications must enable foreign-key enforcement separately for each database connection. Its default can be changed at compile time and could change in a future release. Set the desired behavior explicitly when a connection opens. SQLite’s pragma reference describes the setting.

Imagine a request handler and an ingestion worker opening separate connections to the same database. Enabling enforcement in the handler does not configure the worker. If the worker writes a message with a nonexistent conversation ID while its connection has enforcement off, the declared relationship will not stop that write. This follows from SQLite’s per-connection rule.

Enable it before transactions

Run PRAGMA foreign_keys = ON; as part of every connection’s setup. Then query PRAGMA foreign_keys; and require a result of 1. A result of 0 means enforcement is off. No result means that SQLite lacks foreign-key support in that build or version. SQLite also says changing the setting inside a transaction has no effect, so do this before beginning one. The SQLite guide shows the setting and readback.

What to do

1. Declare the relationship in the schema. Add NOT NULL to the child key if the relationship is mandatory.
2. In every path that opens a connection, enable enforcement before a transaction starts. Read the setting back and fail setup unless it is 1.
3. For a database that may contain earlier writes, run PRAGMA foreign_key_check;. SQLite returns a row for each violation it finds. Investigate those rows before treating the data as consistent. SQLite’s pragma reference documents this check.