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.
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
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.
댓글 없음:
댓글 쓰기