Cot_gyu's Notepad

Use foreign_keys for the current connection's enforcement setting, foreign_key_list for a child table's declared relationships, and foreign_key_check for rows violating declared relationships. Choose by the evidence you need; none of the three answers all questions about a database.

When should I use PRAGMA foreign_keys, foreign_key_list, or foreign_key_check? — A reading map of the checks explained below.

Match the question to the command

Run the command that corresponds to the decision at hand. The output shown here is the documented form, not a local execution result. If you are diagnosing an INSERT that appeared to bypass enforcement, follow the companion article's sequence after selecting the relevant command.

Match the question to the command
Decision	Command	What the result establishes
Is this connection enforcing foreign keys?	PRAGMA foreign_keys;	1 means enabled; 0 means disabled. No row can indicate unavailable foreign key support.
What does this child table declare?	PRAGMA foreign_key_list(child_table);	Rows identify its REFERENCES constraints; no rows means none are listed for that table.
Which stored rows violate declarations?	PRAGMA foreign_key_check;	Each returned row represents a violation; no rows means none were found in the checked scope.

Sources: SQLite Foreign Key Support — 2. Enabling Foreign Key Support, PRAGMA Statements — PRAGMA foreign_keys; PRAGMA foreign_key_list; PRAGMA foreign_key_check

Connect an audit result to its declaration

A foreign_key_check result gives the child table, the child rowid, the referenced parent table, and a constraint identifier. The rowid field is NULL for a WITHOUT ROWID child table. To understand a result, select its child table in foreign_key_list and match the check's identifier to the first column of that list. This matters when several relationships originate in one child table: the parent name alone may not tell you which declaration failed.

For a focused review, PRAGMA foreign_key_check(child_table); limits the check to constraints declared by that child table. Supply the child table even when the missing row would have been in a parent table. If an audit yields many rows, group your investigation by child table and constraint identifier before deciding on repairs; that preserves the link between each bad row and the rule it violates.

Sources: PRAGMA Statements — PRAGMA foreign_key_check; PRAGMA foreign_key_list

Choose scope before drawing conclusions

Use the table argument when the decision concerns one child table, such as reviewing a migration of that table. Omit it when the question concerns declared relationships throughout the database being checked. Record which form was used alongside any reported result. An empty table-scoped result cannot answer whether another child table has violations, and a database-wide result cannot explain an intended relationship that was never declared.

If the goal is schema review, inspect the actual child table with foreign_key_list first. If the goal is data repair, use foreign_key_check and retain its identifiers for investigation. If the goal is an application connection policy, inspect foreign_keys on the connection in question. These choices keep a narrow, meaningful conclusion attached to each output instead of treating a successful command as general proof of referential integrity.

Sources: PRAGMA Statements — PRAGMA foreign_key_list; PRAGMA foreign_key_check; PRAGMA foreign_keys

Avoid a misleading substitute

PRAGMA integrity_check; has a different purpose: SQLite says it does not find foreign key errors. When investigating references between tables, use foreign_key_check for that question. A clean integrity_check result therefore does not replace a foreign key audit.

For repeatable operational reviews, keep the connection setting, schema declaration, and violation audit as three separate pieces of evidence. Note the database and child-table scope for the latter two, and the connection used for the first. If any result conflicts with the application's expectation, investigate that specific gap rather than inferring a cause from the other commands. The companion article provides the ordered diagnosis for an unexpectedly accepted child row.

Sources: PRAGMA Statements — PRAGMA integrity_check; PRAGMA foreign_key_check; PRAGMA foreign_key_list; PRAGMA foreign_keys

Primary sources checked: 2026-10-30. Prepared with AI assistance and a separate evidence review. This is documentation-based guidance, not a hands-on test or performance benchmark.

Read the companion guide

The likely cause is that enforcement was off on the connection that inserted the row, often because PRAGMA foreign_keys = ON ran inside an open transaction and had no effect. If enforcement is active, the declared relationship, child value, and constraint timing determine whether the insert should fail.

Why does SQLite accept a child row with no matching parent after PRAGMA foreign_keys = ON? — A reading map of the checks explained below.

Establish the state of the inserting connection

Read PRAGMA foreign_keys; on the same connection that performed the INSERT. A returned 0 means enforcement is off there; 1 means it is on. Set the required policy when each connection opens and read it back. A setting made in a console or another connection does not establish the state of the application connection. If the query returns no row, SQLite documents that foreign key support may be unavailable in that version or build.

Treat this reading as the first fork in the investigation. With 0, establish when the attempted setting ran. With 1, inspect the row and declaration. If an application uses several connections, identify the one that performed the write before changing setup code; a successful check on a different connection would leave the original symptom unexplained.

Sources: SQLite Foreign Key Support — 2. Enabling Foreign Key Support, PRAGMA Statements — PRAGMA foreign_keys

Determine whether a transaction blocked the change

PRAGMA foreign_keys = ON; has no effect while a BEGIN transaction or SAVEPOINT is pending, and SQLite does not report an error for that attempt. If you know one was open when the setting ran, a later 0 is consistent with this documented no-op. A 0 alone does not prove that transaction timing was the cause: the setting might never have been issued on this connection.

End the current transaction according to the application's data rules, issue the setting outside a transaction, and verify the reading before another write. Check wrapper or connection setup behavior if source code appears to place the pragma before the INSERT: the relevant order is the order on the actual connection. BEGIN transactions generally remain open until COMMIT or ROLLBACK, so a prior unit of work can matter even when the INSERT is much later.

Sources: SQLite Foreign Key Support — 2. Enabling Foreign Key Support, PRAGMA Statements — PRAGMA foreign_keys, Transaction — 2. Transactions

Inspect the relationship and the inserted value

When enforcement reads 1, check that the child table actually declares a REFERENCES relationship to the intended parent. Similar column names or a schema comment do not create a constraint. The companion article explains how to inspect declarations with PRAGMA foreign_key_list(child_table);. If the declaration is absent, repair the schema through an appropriate migration; changing a connection setting cannot supply the missing relationship.

Then look at the value that was inserted. A NULL child key does not require a matching parent. In a composite foreign key, any NULL component removes that matching requirement. If the application requires a parent for every child row, its schema also needs the relevant NOT NULL requirement. A row whose child key is NULL is therefore a different case from a non-NULL key that points to a missing parent.

Sources: SQLite Foreign Key Support — 1. Introduction to Foreign Key Constraints; 4.1. Composite Foreign Key Constraints, PRAGMA Statements — PRAGMA foreign_key_list

Separate a delayed failure from existing bad data

If the constraint is DEFERRABLE INITIALLY DEFERRED within an open transaction, an unmatched row can pass the INSERT and cause COMMIT to fail if still unresolved. PRAGMA defer_foreign_keys can likewise delay enforcement for a transaction. Judge the outcome from the transaction's completion, not only from the INSERT call. If the application adds the parent before COMMIT, the temporary gap can be resolved.

Enabling enforcement does not retroactively reject rows already stored. To audit current violations of declared constraints, use PRAGMA foreign_key_check; and investigate any returned rows before deciding how to repair the data. An empty audit cannot establish that an intended relationship was declared. The companion article covers the audit output and its scope; here the key decision is whether the accepted row was a new write under active enforcement, a temporarily deferred write, or data that predates the setting.

Sources: SQLite Foreign Key Support — 4.2. Deferred Foreign Key Constraints; 2. Enabling Foreign Key Support, PRAGMA Statements — PRAGMA defer_foreign_keys; PRAGMA foreign_key_check