When should I use PRAGMA foreign_keys, foreign_key_list, or foreign_key_check?

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.

A reading map of the checks explained below.

• Match the question to the command
• Connect an audit result to its declaration
• Choose scope before drawing conclusions
• Avoid a misleading substitute

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