SQLite Foreign Keys and Relationships: A Practical Guide (With the Gotchas)

SQLite is the most widely deployed database engine in the world. It's in every phone, every browser, every copy of macOS, and roughly half the apps on your desktop right now. And yet its foreign key story is genuinely weird — weird enough that I've watched experienced engineers lose an afternoon to it.

Here's the short version: foreign keys exist in SQLite, but they're off by default. You have to turn them on, on every connection, or they silently do nothing. And once you do turn them on, you'll discover that ALTER TABLE is so limited that fixing a relationship you got wrong is a multi-step ordeal most tutorials don't even mention.

Most "SQLite foreign keys" articles online cover the happy path: create two tables with a REFERENCES clause, insert a row, move on. That's about 10% of what you actually need to know. This post is the other 90% — the PRAGMA behavior, the ORM configuration, the ALTER TABLE problem, and what to do when you can't change a schema but still need to model a relationship.

Let's go.

How Foreign Keys Work in SQLite (The Default That Surprises People)

SQLite has supported foreign keys since version 3.6.19, released in 2009. That's not a typo — foreign keys have been available for over fifteen years. The catch is that they're disabled by default for backwards compatibility, and the way you enable them is not a schema property or a database setting. It's a runtime flag on the connection.

This is the part that trips people up. You can write a perfectly correct REFERENCES clause, create your tables, insert a row that violates the constraint, and SQLite will happily accept it. No error. No warning. The constraint is just... decorative.

Here's what that looks like. First, the schema:

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id)
);

That FOREIGN KEY (user_id) REFERENCES users(id) is correct SQL. It says: every user_id in orders must point at a real id in users. Now watch what happens when we insert a row that violates it:

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');

-- This references user_id 999, which does not exist.
INSERT INTO orders (id, user_id, total) VALUES (1, 999, 49.99);

-- Result: the insert succeeds. No error.

If you're coming from PostgreSQL or MySQL, this is the moment you stare at the screen. The constraint is right there in the schema. Why didn't it fire?

Because foreign key enforcement is off. You turn it on with a PRAGMA:

PRAGMA foreign_keys = ON;

-- Now try the same insert:
INSERT INTO orders (id, user_id, total) VALUES (2, 999, 49.99);

-- Result: Error: FOREIGN KEY constraint failed

That's the whole mechanic. One line, and your constraints start working. The question is: where do you put that line, and how do you make sure it's always there? That's where it gets annoying.

How to check whether FKs are on right now

Before you do anything else, learn this one query:

PRAGMA foreign_keys;
-- Returns 0 (off) or 1 (on)

Run it on any connection you're debugging. If you're seeing orphaned rows and wondering why your constraints didn't catch them, this is almost always the answer — the connection that did the insert had FKs off.

The PRAGMA Gotcha

Here's the thing that makes the PRAGMA approach genuinely dangerous: it's per-connection, not per-database. Setting PRAGMA foreign_keys = ON on one connection does not affect any other connection, and it does not persist. Open a new connection — even to the same file — and FKs are off again.

This means the setting has to be applied every time you open a connection, by the code that opens it. If you forget, you get silent constraint violations. There's no error log entry, no warning, nothing. The data just goes in.

In practice, this means the configuration lives in your application code or your ORM's setup, not in the schema. And every popular ORM handles it differently, which is its own source of bugs.

Prisma

Prisma enables foreign keys by default when it connects to SQLite. You don't have to do anything — the Prisma engine runs PRAGMA foreign_keys = ON; as part of its connection setup. This is the right default, and it's one of the few things about Prisma's SQLite support that just works out of the box.

If you're using raw queries through $executeRaw / $queryRaw, the same connection pool applies, so FKs are still on. Good.

SQLAlchemy

SQLAlchemy does not enable foreign keys by default for SQLite. This catches a lot of people, because SQLAlchemy is happy to let you define ForeignKey columns in your models and then silently not enforce them.

You have to enable it explicitly with an event listener:

from sqlalchemy import event
from sqlalchemy.engine import Engine

@event.listens_for(Engine, "connect")
def _enable_sqlite_fk(dbapi_connection, connection_record):
    cursor = dbapi_connection.cursor()
    cursor.execute("PRAGMA foreign_keys=ON")
    cursor.close()

That listener fires on every new connection, which is exactly what you want. If you forget this, your ForeignKey declarations are documentation, not constraints.

Drizzle

Drizzle's Node SQLite driver (better-sqlite3 under the hood) also leaves FKs off by default. You enable them when you construct the client:

import { drizzle } from 'drizzle-orm/better-sqlite3';
import Database from 'better-sqlite3';

const sqlite = new Database('app.db');
sqlite.pragma('journal_mode = WAL');
sqlite.pragma('foreign_keys = ON');  // <-- this line

export const db = drizzle(sqlite);

Note that better-sqlite3's .pragma() call applies to that connection. If you're using a pool (rare with better-sqlite3, since it's synchronous and single-connection), you'd need to apply it per connection.

The general rule

Whatever stack you're in, the rule is the same: find the place where connections are created, and set the PRAGMA there. If your ORM doesn't document this clearly, search the issues tab — there's almost always a thread of confused users who discovered FKs were off after shipping.

One more wrinkle: the PRAGMA cannot be changed inside a transaction. PRAGMA foreign_keys = ON is a no-op if you're already in a transaction. It has to be set before any BEGIN. Most ORMs handle this correctly because they set it on connection open, before user code runs, but if you're managing connections manually, keep this in mind.

What SQLite Foreign Keys Support (and Don't)

Once FKs are actually on, SQLite's support is more complete than people give it credit for. The common referential actions all work. Let's go through them with examples.

ON DELETE CASCADE

When the parent row is deleted, all child rows are deleted automatically.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

INSERT INTO users (id, email) VALUES (1, 'alice@example.com');
INSERT INTO orders (id, user_id, total) VALUES (1, 1, 49.99);
INSERT INTO orders (id, user_id, total) VALUES (2, 1, 12.50);

DELETE FROM users WHERE id = 1;

-- Both orders rows are now gone. No manual cleanup needed.

This is the one you'll use most. It's the right default for "child records don't make sense without the parent."

ON UPDATE CASCADE

When the parent's primary key changes, the child's foreign key column is updated to match.

CREATE TABLE accounts (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE transactions (
    id INTEGER PRIMARY KEY,
    account_id INTEGER NOT NULL,
    amount REAL NOT NULL,
    FOREIGN KEY (account_id) REFERENCES accounts(id) ON UPDATE CASCADE
);

INSERT INTO accounts (id, name) VALUES (1, 'Checking');
INSERT INTO transactions (id, account_id, amount) VALUES (1, 1, 100.00);

UPDATE accounts SET id = 100 WHERE id = 1;

-- transactions.account_id is now 100, automatically.

Useful if you ever renumber IDs. Less commonly needed, since most schemas use immutable surrogate keys, but it's there.

SET NULL

When the parent is deleted, the child's FK column is set to NULL (the column must be nullable).

CREATE TABLE projects (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE tasks (
    id INTEGER PRIMARY KEY,
    project_id INTEGER,  -- nullable
    title TEXT NOT NULL,
    FOREIGN KEY (project_id) REFERENCES projects(id) ON DELETE SET NULL
);

INSERT INTO projects (id, name) VALUES (1, 'Migration');
INSERT INTO tasks (id, project_id, title) VALUES (1, 1, 'Write schema');

DELETE FROM projects WHERE id = 1;

-- tasks row still exists, project_id is now NULL.

This is the right choice when the child record is still meaningful without the parent — a task with no project, a comment with no parent post.

RESTRICT and NO ACTION

RESTRICT prevents deletion of the parent if any children exist, and it does so immediately — no deferring, even inside a transaction.

CREATE TABLE invoices (
    id INTEGER PRIMARY KEY,
    total REAL NOT NULL
);

CREATE TABLE invoice_lines (
    id INTEGER PRIMARY KEY,
    invoice_id INTEGER NOT NULL,
    FOREIGN KEY (invoice_id) REFERENCES invoices(id) ON DELETE RESTRICT
);

NO ACTION is the default. In SQLite, NO ACTION and RESTRICT differ in one subtle way: NO ACTION checks the constraint at the end of the statement (so you can reorder deletes within a complex statement), while RESTRICT checks immediately. In practice you almost never notice the difference, but if you want "fail fast, no exceptions," use RESTRICT.

What's NOT supported

A few things worth knowing:

No partial foreign keys. You can't have a FK that only applies when some condition is true (e.g., "enforce this only when status = 'active'). The constraint is all-or-nothing on the column.
No expression-based FKs. The FK must reference actual columns, not expressions.
Deferred constraints are supported but rarely used. You can declare a FK as DEFERRABLE INITIALLY DEFERRED, which means the check is postponed until COMMIT. This is useful for circular references (A references B, B references A) where you need to insert both rows in one transaction. It works, but I've almost never needed it in real schemas — you can usually break the cycle with a nullable column.
The parent column must be a primary key or have a UNIQUE index. SQLite is stricter than some databases here — you can't reference an arbitrary column. It has to be unique.

For the vast majority of schemas, the supported subset is plenty. The gap that bites people isn't the feature list — it's the ALTER TABLE problem, which is next.

The ALTER TABLE Problem

Here's where SQLite's foreign key story gets genuinely painful.

SQLite's ALTER TABLE is famously limited. The full list of what you can do is:

ALTER TABLE ... RENAME TO ... — rename a table
ALTER TABLE ... RENAME COLUMN ... TO ... — rename a column
ALTER TABLE ... ADD COLUMN ... — add a column
ALTER TABLE ... DROP COLUMN ... — drop a column (added in 3.35.0)

That's it. Notably missing from that list:

You cannot add a foreign key constraint to an existing table.
You cannot modify an existing column's type or constraints.
You cannot add a NOT NULL constraint to an existing column.
You cannot change a column's default.

So if you create a table without a FK and later realize you need one, there is no ALTER TABLE orders ADD FOREIGN KEY ... statement. It doesn't exist. The official SQLite documentation describes the workaround, and it's a 12-step process that I'm going to show you in full so you understand why people avoid it.

The 12-step rebuild

The pattern is: create a new table with the schema you want, copy the data over, drop the old table, rename the new one, and re-create any indexes, triggers, or views that depended on the old table. Here it is in SQL:

-- 1. Turn FK enforcement off during the migration (required, because
--    SQLite won't let you alter a table referenced by FKs while FKs are on).
PRAGMA foreign_keys = OFF;

-- 2. Start a transaction.
BEGIN TRANSACTION;

-- 3. Create the new table with the FK constraint you wanted.
CREATE TABLE orders_new (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

-- 4. Copy the data over, filtering out any orphaned rows first.
INSERT INTO orders_new (id, user_id, total)
SELECT id, user_id, total FROM orders
WHERE user_id IN (SELECT id FROM users);

-- 5. Drop the old table.
DROP TABLE orders;

-- 6. Rename the new table to the original name.
ALTER TABLE orders_new RENAME TO orders;

-- 7. Recreate any indexes that were on the old table.
CREATE INDEX idx_orders_user_id ON orders(user_id);

-- 8. Recreate any triggers (if you had them).
-- CREATE TRIGGER ... (omitted for brevity)

-- 9. Recreate any views that referenced the table (if you had them).
-- CREATE VIEW ... (omitted)

-- 10. Run the foreign key check to confirm everything is consistent.
PRAGMA foreign_key_check;

-- 11. Commit.
COMMIT;

-- 12. Turn FK enforcement back on.
PRAGMA foreign_keys = ON;

That's the official procedure. It works. It is also, objectively, a lot — and it's easy to miss a step. Forget to recreate an index and your queries get slow. Forget foreign_key_check and you ship orphaned rows. Forget to turn FKs back on and you're back to the silent-violation problem.

This is the real reason people get frustrated with SQLite relationships: not because the feature is missing, but because correcting a relationship after the fact is a manual, error-prone process. In Postgres you'd run ALTER TABLE orders ADD CONSTRAINT ... FOREIGN KEY ... and be done in one line. In SQLite you're rebuilding the table.

So what do you actually do about it? There are three realistic options, and they have different tradeoffs.

Modeling Relationships Without ALTER TABLE

Option 1: The 12-step rebuild

This is what I showed above. It's the "correct" answer in the sense that it produces a schema with a real, enforced FK constraint. If you care about referential integrity being enforced at the database level — and you usually should — this is the path.

When to use it:

You own the schema and can take a brief write lock.
The table isn't enormous (copying millions of rows takes time, though SQLite is fast).
You want the constraint enforced even by raw SQL or other tools that touch the DB.

When to avoid it:

You can't afford downtime, even a few seconds.
The table is huge and the copy would take too long.
You don't own the schema (it's a third-party app's database).

The practical tip: script the whole thing, test it on a copy of the database first, and run PRAGMA foreign_key_check before you commit. If it returns rows, you have orphans and you need to decide what to do with them before the migration is safe.

Option 2: Let your ORM handle it at the application layer

Most ORMs let you declare relationships in your model definitions even if the database doesn't have a FK constraint. The ORM enforces the relationship in application code — when you do user.orders, it runs SELECT * FROM orders WHERE user_id = ?, and when you create an order, it makes sure user_id is set.

In Prisma:

model User {
  id     Int     @id @default(autoincrement())
  email  String  @unique
  orders Order[]
}

model Order {
  id      Int  @id @default(autoincrement())
  user_id Int
  user    User @relation(fields: [user_id], references: [id])
  total   Float
}

In SQLAlchemy:

class User(Base):
    __tablename__ = "users"
    id = Column(Integer, primary_key=True)
    orders = relationship("Order", back_populates="user")

class Order(Base):
    __tablename__ = "orders"
    id = Column(Integer, primary_key=True)
    user_id = Column(Integer)  # no ForeignKey() needed for ORM-level relations
    user = relationship("User", back_populates="orders")

The tradeoff: the relationship only exists when you go through the ORM. If someone runs raw SQL, or a different tool connects to the database, or a background job inserts rows directly, nothing stops orphaned user_id values. You're trusting that all writes go through your application. For a lot of small projects that's fine. For anything with multiple writers or external tools touching the DB, it's a leaky guarantee.

Option 3: Define the relationship in your admin tool's UI layer

This is the approach I've ended up using most, and it's worth explaining because it solves a specific problem: you want to work with a relationship (browse joined data, navigate from a parent to its children, build a useful admin interface) without modifying the underlying schema.

The idea is that the relationship is defined in a layer above the database — in whatever tool you use to look at the data — rather than in the schema itself. Your application code is completely unaffected. The schema stays as it is. But when you're managing data, you get the joined views and navigation you'd expect from a "real" relationship.

This is exactly what BaseVolt does. It's a local-first desktop app (macOS and Windows) that acts as an admin panel for SQLite, PostgreSQL, MySQL, and Cloudflare D1. You point it at a database, and for any two tables you can define a relationship in the UI — pick the parent table, the child table, the connecting columns — and BaseVolt treats them as linked for the purposes of browsing, joining, and navigation. No ALTER TABLE. No schema migration. No changes to what your application sees.

This is particularly useful for SQLite, where the alternative is the 12-step rebuild above. If you just want to use a relationship without rewriting the schema, defining it in the UI layer is dramatically less work and zero risk.

There's a free tier (up to 2 data sources) and Pro is $99/year. There's a live demo at demo.basevolt.app if you want to see the relationship UI before installing. And because BaseVolt has a built-in MCP server, you can point Claude or Cursor at your database and ask it to help with schema work — including suggesting where relationships should exist.

Visualizing Relationships in an Admin Panel

Let me walk through what defining a relationship in BaseVolt actually looks like, because the workflow is the thing that makes it useful.

Step 1: Connect your database

Open BaseVolt, click "Add Data Source," and point it at your SQLite .db file. It reads the schema directly — no connection string, no migration, no config. For a database with a dozen tables, this takes a few seconds.

Step 2: Pick the two tables you want to link

In the relationships view, you select a parent table (say, users) and a child table (say, orders). BaseVolt shows you the columns in each. You pick the connecting fields — users.id and orders.user_id — and define the relationship. That's it. No SQL runs against your database to create this. The definition lives in BaseVolt's config, not in your schema.

Step 3: Browse the joined view

Once the relationship is defined, a few things happen automatically:

When you're looking at a row in users, you see a linked section showing all related orders rows. Click through to edit any of them.
When you're looking at a row in orders, you see a link back to the parent user.
You can build a filtered view across both tables without writing a JOIN.

This is the part that's hard to convey in text but obvious the moment you use it: you stop thinking in terms of "run a query to see related data" and start thinking in terms of "click the thing." For anyone who's spent time in the sqlite3 CLI doing SELECT * FROM orders WHERE user_id = 5 over and over, this is a meaningful quality-of-life change.

Step 4: Let the AI help

Because BaseVolt exposes an MCP server, you can connect Claude or Cursor to it and ask questions in natural language: "Which orders have no matching user?" or "Suggest a relationship between these tables." The AI can inspect the schema and the data through the MCP server and propose relationships you might have missed. It's not magic — it's just that the schema introspection is already there, and the MCP server makes it available to the model.

The key point is that none of this touches your schema. If you later decide the relationship was wrong, you delete it in the UI and redefine it. No migration, no rebuild, no risk to production data.

Common Patterns

Let's get concrete about the three relationship patterns you'll model over and over, with SQL for each and how they show up in an admin tool.

One-to-many

The most common pattern. One user has many orders. One project has many tasks. One author has many posts.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    email TEXT NOT NULL UNIQUE
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    user_id INTEGER NOT NULL,
    total REAL NOT NULL,
    created_at TEXT NOT NULL DEFAULT (datetime('now')),
    FOREIGN KEY (user_id) REFERENCES users(id) ON DELETE CASCADE
);

The FK lives on the "many" side. In BaseVolt, this shows up as: viewing a user shows their orders below; viewing an order shows a link to its user. One click in either direction.

Many-to-many (junction table)

Many students are enrolled in many courses. You need a third table — a junction table — that holds one row per pairing.

CREATE TABLE students (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL
);

CREATE TABLE courses (
    id INTEGER PRIMARY KEY,
    title TEXT NOT NULL
);

CREATE TABLE enrollments (
    student_id INTEGER NOT NULL,
    course_id INTEGER NOT NULL,
    enrolled_at TEXT NOT NULL DEFAULT (datetime('now')),
    PRIMARY KEY (student_id, course_id),
    FOREIGN KEY (student_id) REFERENCES students(id) ON DELETE CASCADE,
    FOREIGN KEY (course_id) REFERENCES courses(id) ON DELETE CASCADE
);

A few things to notice:

The junction table's primary key is a composite of both FK columns. This prevents duplicate enrollments.
Both FKs use ON DELETE CASCADE, so deleting a student or a course cleans up the enrollments automatically.
The junction table can hold extra data (here, enrolled_at).

In an admin tool, this is two relationships: students ↔ enrollments and courses ↔ enrollments. To see a student's courses, you navigate student → enrollments → course. BaseVolt handles this as two hops, which is the honest way to model it — there's no such thing as a "direct" many-to-many in a relational database, only junction tables.

Self-referential

A category has a parent category. An employee has a manager who is also an employee. A comment has a parent comment.

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    parent_id INTEGER,
    FOREIGN KEY (parent_id) REFERENCES categories(id) ON DELETE SET NULL
);

The FK points from the table back to itself. parent_id is nullable so a root category can have no parent. ON DELETE SET NULL means deleting a category doesn't delete its children — it just orphans them to the top level. If you wanted deletion to cascade down the tree, you'd use ON DELETE CASCADE instead, but be careful: that deletes an entire subtree in one statement.

Self-referential relationships are the one case where defining them in a UI layer is especially nice, because the "parent" and "child" are the same table. In BaseVolt you'd define a relationship from categories to categories on id ↔ parent_id, and then viewing a category shows both its parent and its children inline.

Debugging FK Issues

Even when FKs are on, things go wrong. You inherit a database with orphaned rows. A migration didn't quite work. A background job inserted bad data while FKs were off. SQLite gives you two PRAGMAs for figuring out what happened.

`PRAGMA foreign_key_check;`

This scans the entire database for any rows that violate a foreign key constraint and returns them. Run it whenever you're suspicious:

PRAGMA foreign_key_check;
-- Returns rows like:
-- orders|42|users|1
-- meaning: table 'orders', rowid 42, violates FK into 'users', constraint #1

Each row tells you the child table, the rowid of the offending row, the parent table, and which FK constraint (by index) was violated. From there you can SELECT * FROM orders WHERE rowid = 42 to see the actual row and decide what to do with it — fix the user_id, delete the row, or insert the missing parent.

This is also what you should run at the end of a 12-step rebuild, before you commit. If it returns nothing, your migration is consistent.

`PRAGMA foreign_key_list(table);`

This shows you the foreign key constraints defined on a specific table:

PRAGMA foreign_key_list(orders);
-- Returns one row per FK, showing:
-- id | seq | table  | from     | to | on_update | on_delete | match
-- 0  | 0   | users  | user_id  | id | NO ACTION | CASCADE   | NONE

Useful when you've forgotten what constraints a table has — especially on a database you didn't design. The from and to columns tell you which local column points to which parent column, and on_delete / on_update tell you the actions.

Finding orphaned rows manually

If you want to find orphaned rows for a specific relationship without scanning the whole DB:

SELECT o.*
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL;

This is the classic "find children with no parent" query. It's faster than foreign_key_check if you only care about one relationship, and it gives you the full child rows instead of just rowids.

A debugging workflow

When something feels wrong with relationships, here's the order I'd work through:

Run PRAGMA foreign_keys; on the connection that did the write. If it's 0, that's your answer.
Run PRAGMA foreign_key_check; to see the damage.
For each violation, decide: fix the child, delete the child, or insert the missing parent.
Run PRAGMA foreign_key_list(child_table); to confirm the constraint is what you think it is.
Fix the connection setup so FKs are on everywhere, going forward.

The most common root cause, by far, is step 1. I'd estimate 80% of "my SQLite foreign keys aren't working" issues are just a connection with the PRAGMA off.

The Bottom Line

SQLite's foreign key support is fine. It's not missing features that cause problems — it's the defaults and the ergonomics. Specifically:

FKs are off by default. Set PRAGMA foreign_keys = ON on every connection, in the code that creates the connection. Verify with PRAGMA foreign_keys; when debugging.
The PRAGMA is per-connection. Your ORM's docs will tell you how it handles this. If they don't, assume it doesn't, and add an event hook.
ALTER TABLE can't add FKs. Correcting a missing relationship means a 12-step table rebuild, or defining the relationship at the application/UI layer instead.
The supported referential actions are complete enough for real use. CASCADE, SET NULL, RESTRICT, NO ACTION all work. Deferred constraints exist if you need them.
Debugging is two PRAGMAs. foreign_key_check and foreign_key_list will tell you everything you need.

And if you just want to work with relationships in SQLite without rewriting schemas — browse joined data, navigate between parent and child rows, build an admin interface — define them in the UI layer instead of fighting ALTER TABLE. That's exactly what BaseVolt is for: point it at your database, define the relationships visually, and get a usable admin panel without touching your schema or your app code.

Try it at basevolt.app — no signup, no credit card. There's a live demo at demo.basevolt.app if you want to poke around first.

If you found this useful, I write about databases, local-first software, and the unglamorous parts of building dev tools. Find me on X.