How to check foreign key

Read the declared constraint with PRAGMA foreign_key_list(orders), then read whether anything enforces it with PRAGMA foreign_keys. In the sqlite3 shell that second pragma answers 0, so an order pointing at customer 999 is accepted. PRAGMA foreign_key_check then lists the orphan the schema promised to prevent.

Why check this

A foreign key in a schema file is two separate things, and testers routinely check only the first. One is the declaration: this column points at that table. The other is enforcement: some component refuses a write that breaks the declaration. SQLite ships them apart. The declaration lives in the file; enforcement is a per-connection switch that the library leaves off, and each client decides for itself whether to turn it on.

Run this after any change to the schema, after a restore, and on every service that opens its own connection. A driver, an ORM, a migration runner and a support engineer with a shell are four connections, and each decides for itself.

The failure it prevents is a table full of rows pointing at records deleted months ago. Nothing errors. Reports under-count because the join drops those rows, the delete path never fired its cascade, and the migration that adds the real constraint fails on production data the day it runs.

Prerequisites

• sqlite3 3.50.6, 32-bit. The binary on PATH here is the Android SDK platform-tools build at /c/Android/sdk/platform-tools/sqlite3.exe. Run which sqlite3 and record the answer, because step 5 reads the same pragma from another client and gets another number.
• PostgreSQL and MySQL with InnoDB enforce foreign keys by default, so the pragma steps are SQLite specific, and foreign_key_check has no direct equivalent there.
• Node 22.23.2, whose bundled node:sqlite links SQLite 3.51.3, as a second client against the same file.
• The SQLite foreign key documentation, section 2, for the default and its reason.
• The fixture used below, saved as shop.sql and loaded with sqlite3 shop.db < shop.sql.

CREATE TABLE customer (
  id      INTEGER PRIMARY KEY,
  email   TEXT UNIQUE,
  country TEXT NOT NULL
);
CREATE TABLE orders (
  id          INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customer(id),
  total_cents INTEGER NOT NULL
);
INSERT INTO customer (id, email, country) VALUES
  (1, 'ana@example.com', 'UA'),
  (2, 'bo@example.com',  'PL');
INSERT INTO orders (id, customer_id, total_cents) VALUES
  (10, 1, 2500),
  (11, 2, 900);

One more file, fk-default.mjs, for step 5.

import { DatabaseSync } from 'node:sqlite';
const db = new DatabaseSync('shop.db');
console.log('foreign_keys =', db.prepare('PRAGMA foreign_keys').get().foreign_keys);
console.log('sqlite_version =', db.prepare('SELECT sqlite_version() AS v').get().v);
db.close();

Steps

1. 1

   Step 1.

   Read what the schema declares for the child table.

   sqlite3 shop.db ".mode box" "PRAGMA foreign_key_list(orders);"
   

   ┌────┬─────┬──────────┬─────────────┬────┬───────────┬───────────┬───────┐
   │ id │ seq │  table   │    from     │ to │ on_update │ on_delete │ match │
   ├────┼─────┼──────────┼─────────────┼────┼───────────┼───────────┼───────┤
   │ 0  │ 0   │ customer │ customer_id │ id │ NO ACTION │ NO ACTION │ NONE  │
   └────┴─────┴──────────┴─────────────┴────┴───────────┴───────────┴───────┘

   One key: orders.customer_id points at customer.id. on_delete is NO ACTION, so nothing cascades. An empty result means the table declares no keys, whatever the diagram shows.

2. 2

   Step 2.

   Ask whether this connection enforces that declaration, then write a row that breaks it.

   sqlite3 shop.db ".mode box" "PRAGMA foreign_keys; INSERT INTO orders (id, customer_id, total_cents) VALUES (12, 999, 4200); SELECT changes() AS rows_inserted;"
   

   ┌──────────────┐
   │ foreign_keys │
   ├──────────────┤
   │ 0            │
   └──────────────┘
   ┌───────────────┐
   │ rows_inserted │
   ├───────────────┤
   │ 1             │
   └───────────────┘

   The pragma returns 0 and the insert returns 1. There is no customer 999, no error, and no warning. This is the most important line on the page: the constraint is declared and this client is enforcing none of it.

3. 3

   Step 3.

   Find the rows that got in. This pragma works whatever the enforcement setting is.

   sqlite3 shop.db ".mode box" "PRAGMA foreign_key_check;"
   

   ┌────────┬───────┬──────────┬──────┐
   │ table  │ rowid │  parent  │ fkid │
   ├────────┼───────┼──────────┼──────┤
   │ orders │ 12    │ customer │ 0    │
   └────────┴───────┴──────────┴──────┘

   One row per violation: the child table, the rowid of the offending row, the parent table, and the index of the key in the foreign_key_list output above. No rows means no orphans.

4. 4

   Step 4.

   Turn enforcement on and run the same insert again.

   sqlite3 shop.db "PRAGMA foreign_keys = ON; INSERT INTO orders (id, customer_id, total_cents) VALUES (13, 999, 4200);"
   

   Error: stepping, FOREIGN KEY constraint failed (19)

   The same statement, the same file, one pragma apart. The message names no table and no column, unlike the unique constraint error, so log the statement with it. The orphan from step 2 is still there: switching the pragma on does not re-examine stored rows.

5. 5

   Step 5.

   Open the same file from a different client and read the pragma again.

   node --no-warnings fk-default.mjs
   

   foreign_keys = 1
   sqlite_version = 3.51.3

   Node's DatabaseSync turns the pragma on when it connects, and it links a newer SQLite than the shell on PATH. The same file therefore enforces foreign keys for the application and not for the engineer debugging it in a terminal. The default belongs to the client, not to the database.

How to read the result

| What you see | What it means | What to do | | --- | --- | --- | | foreign_key_list returns no rows | The table declares no foreign key | The relationship exists only in the diagram. Add it in a migration. | | PRAGMA foreign_keys returns 0 | This connection enforces nothing | Set it to ON at connect, in every client, and assert it in a health check. | | PRAGMA foreign_keys returns 1 | This connection enforces | Confirm the setting is applied at connect, not once by hand. | | foreign_key_check returns rows | Orphans are already stored | Fix or delete them before the migration that adds enforcement. | | FOREIGN KEY constraint failed (19) | Enforcement is live and refused a write | Expected. Assert on it in the test for the delete path. | | on_delete is NO ACTION | No cascade, no SET NULL | The application owns the delete order. Test it. |

Common mistakes

What to check next

• How to check database integrity: the engine check that reports ok on the orphan row you just found.
• How to test unique constraint: the other constraint that looks stronger in the schema than it is in the data.
• How to check duplicate rows in sql: the grouped counts that find the rows a new constraint will reject.
• How to test database migration: where enforcement is switched on, and where it fails on real data.

FAQ

How do I check a foreign key in SQL?

Read the declaration with PRAGMA foreign_key_list(<table>) in SQLite, or information_schema.referential_constraints in PostgreSQL and MySQL. Then check enforcement separately. In SQLite that is PRAGMA foreign_keys, and it answers per connection.

Why does SQLite default foreign keys to off?

Backwards compatibility. Enforcement arrived in version 3.6.19, and switching it on by default would have broken databases written before it existed. Clients override it: the shell here reported 0 and node:sqlite reported 1 against one file.

Does PRAGMA foreign_key_check need enforcement switched on?

No. It scans the stored rows against the declared keys whatever the pragma says, which is why it is the right tool on a restored backup or before a migration. It returns one row per violation and nothing when the data is clean.

Where should the pragma be set?

In the connection setup of every client, as the first statement after connecting. Setting it in one service leaves the others writing orphans into the same file, and setting it inside a transaction does nothing at all.

Verified