0%

SQLite Foreign Key Constraint Must be Enabled Manually

December 27, 2025

Sql

Sqlite

Contents

1. Overview

SQLite has foreign keys disabled by default, unlike PostgreSQL or MySQL. This requires explicit configuration in every database connection to enable foreign key constraints and CASCADE deletion.

2. How to Check if Foreign Keys Are Enabled

PRAGMA foreign_keys;

Returns:

  • 0 = Foreign keys are disabled (default)
  • 1 = Foreign keys are enabled

3. How to Enable Foreign Keys

3.1. Method 1: Per-Connection PRAGMA

Execute this SQL command every time you connect to the database:

PRAGMA foreign_keys = ON;

3.2. Method 2: Connection URL Parameter

Add foreign_keys=true to your connection string:

file:path/to/database.db?foreign_keys=true
jdbc:sqlite:path/to/database.db?foreign_keys=true

This automatically executes PRAGMA foreign_keys = ON for every connection.

3.3. Method 3: TablePlus Bootstrap Command

For SQL client tools like TablePlus, you can configure a bootstrap command that runs automatically on every connection:

  1. Open TablePlus connection dialog
  2. Click "Bootstrap commands..." button
  3. Add the following command:
PRAGMA foreign_keys = ON;
  1. Click "Save"
  2. Click "Connect"

This ensures foreign keys are enabled automatically every time you connect with TablePlus.

4. Important Notes

  1. Foreign keys are per-connection settings - Each database connection must enable foreign keys independently
  2. Table structure is permanent - CASCADE constraints must be defined when creating tables
  3. Existing tables may need recreation - If tables were created without CASCADE, you must drop and recreate them

5. References