I declared a FOREIGN KEY in SQLite. Why can an order for a missing customer still get in?

You wrote REFERENCES on a SQLite table, and an order pointing at a customer who does not exist is still saved without an error. The constraint is not wrong. SQLite's foreign key check is off by default and has to be turned on for each connection. After reading this you can confirm whether the current connection enforces it, avoid the cases where turning it on silently does nothing, and find the bad rows already in the file.

The examples were run on 2026-11-01 on macOS 27.0.1. The sqlite3 shell is the 3.54.0 that ships with macOS, Python is the system 3.9.6 (SQLite 3.54.0) and a uv-installed 3.12.14 (SQLite 3.53.1), and Node.js is v24.21.0 (SQLite 3.53.4). The customers and orders are fictional. Other operating systems and ORMs were not run.

The constraint is stored, but the check is off

Two ordinary tables, where an order points at a customer.

CREATE TABLE customers (
  id   INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL
              REFERENCES customers(id) ON DELETE CASCADE,
  item        TEXT NOT NULL
);
INSERT INTO customers VALUES (1, 'Mina');
INSERT INTO orders VALUES (100, 1, 'keyboard');

In the examples below, each sqlite3 shop.db "..." line is one new connection. On a new connection PRAGMA foreign_keys was 0, and an INSERT pointing at the missing customer 999 finished with no error and exit code 0. SELECT then showed 101|999|ghost.

The SQLite documentation says foreign key constraints are off by default for backwards compatibility and must be enabled for each connection. The default has been OFF since 3.6.19, and because that might change, the documentation tells applications to set it themselves and not rely on the default.

Even with the check off, the schema keeps the constraint. PRAGMA foreign_key_check lists the rows that violate it, and on a connection with the check off it still returned orders|101|customers|0. The four fields are the child table, that row's rowid, the parent table and the constraint number.

Turning it on covers one connection, and old rows stay

On a connection that ran PRAGMA foreign_keys = ON first, the same kind of INSERT was refused with FOREIGN KEY constraint failed and exit code 1. The next command is a new connection, though, and the value was back to 0. The setting belongs to the connection, not to the database file. An app with a connection pool has to run this statement at the point where each new connection is created.

On the connection with the check on, order 101 from earlier could still be read and still appeared in foreign_key_check. Turning the check on did not go back over existing rows. Before enabling it on a file already in use, find the violating rows with this command first. Whether to delete them or restore the parent depends on the data, so this note does not decide that.

Two silent failures, and a CASCADE that does not run

The first is inside a transaction. After BEGIN, running PRAGMA foreign_keys = ON and reading the value gave 0. The documentation also says that this PRAGMA does nothing inside a transaction and returns no error.

The second is a typo. PRAGMA foreign_key = ON, with the s missing, also finished without an error, and the value was 0. The documentation says SQLite simply ignores an unknown PRAGMA. In both cases the statement that turns the check on tells you nothing; only reading the value back does.

ON DELETE CASCADE did not run on a connection with the check off either. After customer 1 was deleted, order 100 remained and foreign_key_check grew to two lines. On a connection with the check on, deleting customer 2 also deleted order 103, so the count was 0.

The same file behaves differently depending on who opens it

The default depended on what wraps SQLite. Python's sqlite3 gave 0 on both 3.9.6 and 3.12.14. Node.js's node:sqlite gave 1 and refused the same INSERT. The v24 documentation also lists true as the default of enableForeignKeyConstraints, and passing false gave 0 and let the INSERT through. The documentation marks node:sqlite as stability 1.2 (release candidate).

So with the same shop.db and the same kind of statement, Python's default settings accepted the row and Node.js's default settings refused it. An app using node:sqlite with maintenance scripts run through Python or the sqlite3 shell meets exactly this difference. SQLite also has a compile option that sets the default, SQLITE_DEFAULT_FOREIGN_KEYS, and the documentation of the npm package better-sqlite3 says it builds with this set to 1 (not run). A version number will not tell you, so reading the value is quicker.

In Python, the autocommit setting swallows the PRAGMA

Python needs one more check. Under the default (legacy) transaction control, an INSERT opens a transaction implicitly. So a PRAGMA run after an INSERT and before commit() stayed at 0, and after commit() it became 1.

autocommit=False, added in 3.12, makes this plainer. The Python documentation says this value keeps a transaction open at all times and marks it as the recommended value. So running the PRAGMA right after connecting did not help. commit() did not help either, because it opens the next transaction straight away.

conn = sqlite3.connect(":memory:", autocommit=False)
conn.execute("PRAGMA foreign_keys = ON")
print(conn.execute("PRAGMA foreign_keys").fetchone()[0])  # 0

The documentation also says the default of autocommit will change to False in a future release (in the 3.12.16 and 3.15.2 pages the legacy behaviour is still the default). Two approaches worked on 3.12.14. Both need Python 3.12 or later, the version that added autocommit and setconfig. Connect with autocommit=True, run the PRAGMA and then set conn.autocommit = False; or use conn.setconfig(sqlite3.SQLITE_DBCONFIG_ENABLE_FKEY, True). Both gave 1. The first, wrapped in one function, looks like this.

def connect(path):
    conn = sqlite3.connect(path, autocommit=True)
    conn.execute("PRAGMA foreign_keys = ON")
    if conn.execute("PRAGMA foreign_keys").fetchone()[0] != 1:
        raise RuntimeError("foreign keys are not enforced")
    conn.autocommit = False
    return conn

A connection opened by this function refused the order for the missing customer with IntegrityError.

DEV NOTES

Foreign keys: the default depends on who opens the file

Reading PRAGMA foreign_keys and seeing 1 is the reliable check

• Default on a new connection (observed)

  • sqlite3 shell 3.54.0: 0 (off)
  • Python sqlite3 3.9.6, 3.12.14: 0 (off)
  • Node.js node:sqlite v24.21.0: 1 (on)

• Silently not turned on (observed)

  • The PRAGMA after BEGIN
  • PRAGMA foreign_key, with the s missing
  • Python: after an INSERT, before commit()
  • Python: on an autocommit=False connection

• Where the app opens a connection

  PRAGMA foreign_keys = ON;

  • Turn it on for each connection (it is not stored in the file)
  • Read it back and check for 1
  • On a file in use, find violating rows first with foreign_key_check (old rows are not re-checked)

Observed on macOS 27.0.1 on 2026-11-01. Other operating systems, ORMs and better-sqlite3 were not run.

Open SQLite from one place in your app, turn on PRAGMA foreign_keys there, and check that it reads back as 1. If the file is already in use, run PRAGMA foreign_key_check once before that and see how many violating rows there are.