SQLite Foreign Key Enforcement: Enable and Validate (2026)

SQLite doesn’t enforce foreign key constraints by default, which means your database can easily end up in a state with orphaned records or invalid references.

Let’s see how this plays out in the wild. Imagine you have two tables: users and posts.

CREATE TABLE users (
    user_id INTEGER PRIMARY KEY AUTOINCREMENT,
    username TEXT NOT NULL UNIQUE
);

CREATE TABLE posts (
    post_id INTEGER PRIMARY KEY AUTOINCREMENT,
    author_id INTEGER,
    title TEXT NOT NULL,
    FOREIGN KEY (author_id) REFERENCES users(user_id)
);

Normally, if you try to delete a user who has posts, SQLite would prevent it if foreign key enforcement was on. But without it, watch what happens:

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Create tables (as above)
cursor.execute("CREATE TABLE users (user_id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE);")
cursor.execute("CREATE TABLE posts (post_id INTEGER PRIMARY KEY AUTOINCREMENT, author_id INTEGER, title TEXT NOT NULL, FOREIGN KEY (author_id) REFERENCES users(user_id));")

# Insert a user and a post
cursor.execute("INSERT INTO users (username) VALUES (?)", ('alice',))
user_id = cursor.lastrowid
cursor.execute("INSERT INTO posts (author_id, title) VALUES (?, ?)", (user_id, 'My First Post'))

conn.commit()

print(f"User ID for alice: {user_id}")

# Now, delete the user WITHOUT foreign key enforcement enabled
cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id,))
conn.commit()

# Verify the post still exists, but its author_id is now an orphan
cursor.execute("SELECT * FROM posts WHERE post_id = 1")
print(cursor.fetchone())

Output:

User ID for alice: 1
(1, 1, 'My First Post')

See that? The posts table still has a record, but author_id 1 no longer refers to any valid user. This is the orphaned record problem.

To enable foreign key enforcement, you need to issue a PRAGMA statement after connecting to the database but before performing any operations that rely on foreign keys.

import sqlite3

conn = sqlite3.connect(':memory:')
cursor = conn.cursor()

# Enable foreign key enforcement
cursor.execute("PRAGMA foreign_keys = ON;")

# Create tables (as above)
cursor.execute("CREATE TABLE users (user_id INTEGER PRIMARY KEY AUTOINCREMENT, username TEXT NOT NULL UNIQUE);")
cursor.execute("CREATE TABLE posts (post_id INTEGER PRIMARY KEY AUTOINCREMENT, author_id INTEGER, title TEXT NOT NULL, FOREIGN KEY (author_id) REFERENCES users(user_id));")

# Insert a user and a post
cursor.execute("INSERT INTO users (username) VALUES (?)", ('alice',))
user_id = cursor.lastrowid
cursor.execute("INSERT INTO posts (author_id, title) VALUES (?, ?)", (user_id, 'My First Post'))

conn.commit()

print(f"User ID for alice: {user_id}")

# Attempt to delete the user WITH foreign key enforcement enabled
try:
    cursor.execute("DELETE FROM users WHERE user_id = ?", (user_id,))
    conn.commit()
except sqlite3.IntegrityError as e:
    print(f"Error: {e}")

# Verify the user was NOT deleted and the post is still valid
cursor.execute("SELECT * FROM users WHERE user_id = ?", (user_id,))
print(f"User: {cursor.fetchone()}")
cursor.execute("SELECT * FROM posts WHERE post_id = 1")
print(f"Post: {cursor.fetchone()}")

Output:

User ID for alice: 1
Error: FOREIGN KEY constraint failed
User: (1, 'alice')
Post: (1, 1, 'My First Post')

This time, the DELETE statement failed with an IntegrityError, and the user and their associated post remain intact. This is the desired behavior. The PRAGMA foreign_keys = ON; statement tells SQLite to actively check and enforce the referential integrity defined by your FOREIGN KEY constraints.

Why does this work?

When foreign_keys is set to ON, SQLite modifies its query execution plan. Before allowing an operation that might violate a foreign key (like deleting a parent record or updating its primary key), it performs a lookup on the referencing table. If it finds any matching records in the child table, it raises an IntegrityError, preventing the operation. Conversely, PRAGMA foreign_keys = OFF; (the default) skips these checks for performance.

How to Validate Enforcement:

Check the PRAGMA status: You can query the current setting:
```
PRAGMA foreign_keys;
```
This will return 1 if enabled, 0 if disabled.
Test constraint violations: The most reliable way is to intentionally try to create a violation and ensure you get an IntegrityError.
- Try inserting a post with an author_id that doesn’t exist in the users table.
- Try deleting a user that has associated posts.
- Try updating a user_id in the users table if there are corresponding author_ids in posts (if ON UPDATE CASCADE etc. are not specified).

Common Pitfalls and Nuances:

Per-connection: The PRAGMA foreign_keys setting is specific to each database connection. If you open multiple connections to the same SQLite database, you must enable foreign keys on each connection independently. This is a crucial point often missed when using connection pools or multiple application threads.
Order matters: The PRAGMA must be executed after the PRAGMA foreign_keys is set to ON and before any DML (Data Manipulation Language) statements that rely on foreign key integrity.
No automatic cascade (by default): While ON DELETE CASCADE or ON UPDATE CASCADE can be specified in the CREATE TABLE statement, SQLite’s default behavior without these is to simply prevent the delete/update operation if a violation would occur. Enabling PRAGMA foreign_keys = ON is what makes these CASCADE, SET NULL, or RESTRICT actions actually trigger.
WAL Mode: While not directly related to enforcement, using Write-Ahead Logging (WAL) mode (PRAGMA journal_mode=WAL;) is generally recommended for SQLite databases, especially when concurrency is a concern. It can improve performance and reliability, but doesn’t replace the need for PRAGMA foreign_keys = ON;.

The next step after ensuring your foreign keys are enforced is understanding how to manage the referential actions like ON DELETE CASCADE or ON UPDATE SET NULL for more complex data management scenarios.

More Deep Dives in Sqlite