Why might SQLite accept a foreign-key definition but not enforce it?

A foreign-key clause in a table definition describes the relationship; it does not by itself prove that SQLite is enforcing it. SQLite needs foreign-key support in its build, and enforcement must also be enabled at runtime with PRAGMA foreign_keys. The setting applies to each database connection separately, so one connection can enforce the relationship while another does not. [S1]

Don't rely on a presumed default. Set the pragma explicitly on the connection your application will use, then query PRAGMA foreign_keys on that same connection. Check that the result reports enforcement as enabled. If the query returns no row, the SQLite build may lack the relevant foreign-key capability. [S1] [S2]

Set it for each connection, before beginning a transaction or savepoint. SQLite does not change the setting while a transaction or savepoint is pending; attempting to toggle it then is a no-op. [S1] [S2]

For a connection-pool or multi-connection application, treat this as connection setup rather than a one-time database setup step. Check the actual connection that runs the insert you are investigating. A successful check on a different connection does not establish the setting for this one.

Use a disposable in-memory database, or another temporary database you can discard. Run the following statements on one connection, in order. The final insert is intentionally invalid: it refers to parent ID 99, which the test has not inserted.

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

CREATE TABLE parent (
  id INTEGER PRIMARY KEY
);

CREATE TABLE child (
  parent_id INTEGER REFERENCES parent(id)
);

INSERT INTO child (parent_id) VALUES (99);

Inspect the pragma result to confirm enforcement is enabled. If foreign-key support is available and enforcement is enabled, the last statement should fail with a foreign-key constraint error. If the pragma indicates enforcement is off, it is off for that connection; if it returns no row, the build may not support the feature. [S1] [S2]

If your SQL tool stops after the expected insert error, that is fine: the failed insert is the test. Do not run this example against production tables. This demonstrates the connection’s behavior for this simple case; it does not verify every relationship or every behavior in an application.

Put the explicit setting and verification in the code path that creates or opens each connection, before application code starts a transaction. Inspect the pragma result during troubleshooting, and compare it with the connection used for the insert that appears to ignore the constraint. If the setting reports that enforcement is enabled but an insert still seems accepted, reproduce the specific table relationship in a disposable database and verify that the test uses the same SQLite library and connection setup.

Use this setup checklist:

1. Open the connection.
2. Before any transaction or savepoint, run PRAGMA foreign_keys = ON.
3. On that same connection, query PRAGMA foreign_keys and confirm that enforcement is reported as enabled.
4. If there is no result row, investigate whether the SQLite build supports foreign keys; if enforcement is reported as off, check when and where the setting is applied. [S1]
5. Repeat the check for every connection your application opens.
6. Confirm expected rejection with a disposable invalid-child insert, not a production-data experiment.

The pragma check establishes the setting for that connection, not that every schema definition or application path is correct. These checks also do not establish why a particular application behaved as it did; inspect that application’s actual connection and test case.