SQLite / PostgreSQL IntegrityError: FOREIGN KEY constraint failed

Verified FixPython 3.10+SQLite3 / SQLAlchemy 2.0+ / Django ORMSilo: database

Quick Fix / Solution Rapide

Ensure the referenced parent record exists before inserting child rows, or configure ON DELETE CASCADE to prevent orphaned foreign keys.

Root Cause Analysis

This error occurs when Python executes an SQL INSERT, UPDATE, or DELETE statement via SQLite, PostgreSQL, or SQLAlchemy that violates a foreign key relational integrity constraint established in the database schema.

1. Inserting Orphaned Child Records

When a child table defines FOREIGN KEY(author_id) REFERENCES authors(id), inserting a record with author_id = 999 fails if no row in authors has id = 999. The relational engine halts execution and raises IntegrityError: FOREIGN KEY constraint failed.

2. Deleting Parent Records Without Cascade Rules

Attempting to delete a row from a parent table (DELETE FROM authors WHERE id = 1) when child records in books still reference author_id = 1 violates referential integrity unless ON DELETE CASCADE or ON DELETE SET NULL is specified.

3. SQLite PRAGMA foreign_keys Activation

In SQLite, foreign key enforcement is disabled by default for backward compatibility. When developers enable PRAGMA foreign_keys = ON;, previously unvalidated insertion bugs surface immediately.

4. Bulk Insert Ordering in Migrations and Seed Scripts

Seeding database tables in the wrong sequence (e.g. inserting orders before users) triggers constraint failures.

Reproduction Code (MCVE)

Example: Bug Reproduction

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON;')
conn.execute('CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);')
conn.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER, FOREIGN KEY(author_id) REFERENCES authors(id));')

# Inserting child with non-existent author_id raises IntegrityError
conn.execute('INSERT INTO books (id, title, author_id) VALUES (1, "Clean Code", 999);')

Solution 1: Insert Parent Record Prior to Child Insertion

Create the parent row first, obtain its generated primary key, and assign it to the child record.

Example: Recommended Solution

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON;')
conn.execute('CREATE TABLE authors (id INTEGER PRIMARY KEY, name TEXT);')
conn.execute('CREATE TABLE books (id INTEGER PRIMARY KEY, title TEXT, author_id INTEGER, FOREIGN KEY(author_id) REFERENCES authors(id));')

# 1. Insert parent
cursor = conn.cursor()
cursor.execute('INSERT INTO authors (name) VALUES ("Robert C. Martin");')
author_id = cursor.lastrowid

# 2. Insert child with verified parent ID
cursor.execute('INSERT INTO books (title, author_id) VALUES ("Clean Code", ?);', (author_id,))
conn.commit()
print(f'Successfully inserted book with valid author_id: {author_id}')

Solution 2: Configure ON DELETE CASCADE in Table Schema

Add ON DELETE CASCADE to foreign key definitions so deleting parent records automatically cleans up child entries.

Example: Alternative Solution

import sqlite3

conn = sqlite3.connect(':memory:')
conn.execute('PRAGMA foreign_keys = ON;')
conn.execute('CREATE TABLE categories (id INTEGER PRIMARY KEY);')
conn.execute('''
    CREATE TABLE items (
        id INTEGER PRIMARY KEY,
        category_id INTEGER REFERENCES categories(id) ON DELETE CASCADE
    );
''')
conn.execute('INSERT INTO categories VALUES (1);')
conn.execute('INSERT INTO items VALUES (10, 1);')
conn.execute('DELETE FROM categories WHERE id = 1;')  # Automatically cascades deletion
remaining = conn.execute('SELECT COUNT(*) FROM items;').fetchone()[0]
print(f'Child items remaining after cascade: {remaining}')

A common mistake in SQLAlchemy ORM is forgetting to add relationship(cascade='all, delete-orphan') alongside database-level ON DELETE CASCADE. SQLAlchemy tracks objects in-memory; without ORM cascade declarations, SQLAlchemy may attempt to set foreign keys to NULL before deleting, raising constraint errors. Edge cases occur with circular foreign key dependencies between two tables: use deferrable=True, initially='DEFERRED' to delay constraint evaluation until COMMIT. Contrast this error with IntegrityError: UNIQUE constraint failed, which occurs when duplicate values are inserted into unique index columns.

Previous Next