Bagale Pulse

Practical technology guides and perspectives from Adamawa.

Bagale Admin Login

Authorized administrators only.

Forgot password?

SQLite Foreign Keys: Enable and Test Them

By Bagale Pulse

A shop database should not accept an order for a customer who does not exist. In SQLite, declaring a foreign key is only part of that protection: check that enforcement is enabled on the connection doing the work.

This example uses two small tables and invented records. Run it in a new, disposable SQLite database, not in your live customer database. Execute the statements in order; the two failure cases are intentional.

1. Enable enforcement, then read its state

PRAGMA foreign_keys = ON;
PRAGMA foreign_keys;

The second statement should return 1. The assignment turns enforcement on; the separate query checks it. Set this when each connection opens, before starting a transaction. Changing it inside an active transaction has no effect. Do not assume your framework or another connection has enabled it for you.

2. Create a customer and an order

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 RESTRICT
);
INSERT INTO customers VALUES (1, 'Demo customer');
INSERT INTO orders VALUES (101, 1);
SELECT id, customer_id FROM orders;

The query returns order 101 with customer 1. The reference points to the customer's primary key. NOT NULL also matters: this shop requires every order to have a customer.

3. Prove that an orphan row is rejected

INSERT INTO orders VALUES (102, 999);

There is no customer 999. Expect a foreign-key constraint error, not a second order. Your SQLite tool may display an error code as well as the message. Run the SELECT again: order 101 should still be the only row.

Also try this statement by itself:

DELETE FROM customers WHERE id = 1;

It should fail because order 101 still refers to that customer. We chose ON DELETE RESTRICT deliberately. A cascade would have different consequences, so do not add it simply to make a deletion succeed. Decide how your application should preserve order history first.

4. Check existing data separately

PRAGMA foreign_key_check;

In the clean example it returns no rows. Turning enforcement on does not repair violations already stored while it was off. On a copy of an older database, this check reports the affected child table, its rowid, the parent table and the foreign-key identifier.

For example, an orders row with id 103 pointing to missing customer 999 would produce orders | 103 | customers | 0 in this schema. Investigate the original record before deciding whether to correct the reference, restore the legitimate parent or remove an invalid test record.

If the bad insert unexpectedly succeeds

  • Query PRAGMA foreign_keys; on the same connection that made the insert.
  • Check whether you attempted to enable enforcement after a transaction had already started.
  • Inspect the actual schema with PRAGMA foreign_key_list(orders);. A column name alone does not declare a relationship.
  • Confirm that your connection opened the intended database file.

The useful test has three parts: a valid order succeeds, an orphan order fails, and the consistency check is empty. Keep these checks in your application's database tests so a new connection setup does not silently remove the protection.

Official references: SQLite foreign-key support and PRAGMA foreign_key_check.

Worked SQL example checked with Python's SQLite library on 10 October 2026. Error formatting can differ between SQLite clients.