FOREIGN KEY constraint failed

This error occurs when a foreign key reference points to a row that doesn't exist or when deleting a row that's still referenced. Learn how to fix referential integrity issues.

The FOREIGN KEY constraint failed error means you're trying to create a reference to a non-existent row, or delete/update a row that other rows depend on.

Understanding the Error

Foreign keys enforce referential integrity - they ensure relationships between tables stay valid.

SQLITE_CONSTRAINT: FOREIGN KEY constraint failed

Important: Enable Foreign Keys First

SQLite has foreign keys disabled by default! You must enable them:

SQL

PRAGMA foreign_keys = ON;

In application code, enable this on every connection:

JAVASCRIPT

const db = new Database('myapp.db');
db.pragma('foreign_keys = ON');

Common Causes

1. Inserting Reference to Non-Existent Row

SQL

CREATE TABLE users (id INTEGER PRIMARY KEY);
CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id)
);

-- Fails: user 999 doesn't exist
INSERT INTO posts (user_id) VALUES (999);

2. Deleting a Referenced Row

SQL

-- User 1 has posts
INSERT INTO users VALUES (1);
INSERT INTO posts (user_id) VALUES (1);

-- Fails: posts still reference this user
DELETE FROM users WHERE id = 1;

3. Updating a Referenced Primary Key

SQL

-- Fails: posts reference user 1
UPDATE users SET id = 100 WHERE id = 1;

4. Wrong Column Reference

Referencing a column that isn't the primary key or unique:

SQL

-- email is not unique/primary key
CREATE TABLE posts (
  user_email TEXT REFERENCES users(email)  -- Problematic
);

How to Fix It

Solution 1: Insert Parent Row First

Always create the referenced row before the referencing row:

SQL

-- First: create the user
INSERT INTO users (id, name) VALUES (1, 'Alice');

-- Then: create posts for that user
INSERT INTO posts (user_id, title) VALUES (1, 'Hello World');

Solution 2: Use CASCADE for Deletes

Automatically delete child rows when parent is deleted:

SQL

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE CASCADE
);

-- Now this works: posts are deleted too
DELETE FROM users WHERE id = 1;

Solution 3: Use SET NULL for Deletes

Set foreign key to NULL when parent is deleted:

SQL

CREATE TABLE posts (
  id INTEGER PRIMARY KEY,
  user_id INTEGER REFERENCES users(id) ON DELETE SET NULL
);

-- Posts remain but user_id becomes NULL
DELETE FROM users WHERE id = 1;

Solution 4: Delete Children First

Manually delete in the right order:

SQL

-- Delete posts first
DELETE FROM posts WHERE user_id = 1;

-- Then delete the user
DELETE FROM users WHERE id = 1;

Solution 5: Use INSERT OR IGNORE

Skip inserts that would violate constraints:

SQL

-- Silently skips if user_id doesn't exist
INSERT OR IGNORE INTO posts (user_id, title) VALUES (999, 'Test');

Solution 6: Verify Reference Exists

Check before inserting:

SQL

-- In application code
const userExists = db.prepare(
  'SELECT 1 FROM users WHERE id = ?'
).get(userId);

if (userExists) {
  db.run('INSERT INTO posts (user_id, title) VALUES (?, ?)',
    [userId, title]);
} else {
  throw new Error('User not found');
}

Foreign Key Options

ON DELETE Options

SQL

CREATE TABLE posts (
  user_id INTEGER REFERENCES users(id)
    ON DELETE CASCADE      -- Delete posts when user deleted
    -- ON DELETE SET NULL  -- Set user_id to NULL
    -- ON DELETE SET DEFAULT -- Set to default value
    -- ON DELETE RESTRICT  -- Prevent deletion (default)
    -- ON DELETE NO ACTION -- Same as RESTRICT
);

ON UPDATE Options

SQL

CREATE TABLE posts (
  user_id INTEGER REFERENCES users(id)
    ON UPDATE CASCADE      -- Update user_id when users.id changes
);

Checking Foreign Key Status

SQL

-- Check if foreign keys are enabled
PRAGMA foreign_keys;

-- List foreign keys for a table
PRAGMA foreign_key_list(posts);

-- Check for violations in existing data
PRAGMA foreign_key_check;
PRAGMA foreign_key_check(posts);  -- Specific table

Best Practices

Always enable foreign keys at connection time
Use CASCADE carefully - Understand what will be deleted
Insert in order - Parent rows before child rows
Delete in reverse order - Child rows before parent rows
Use transactions for multi-table operations
Check for violations before enabling foreign keys on existing data

Fixing Existing Violations

If you have data that violates foreign key constraints:

SQL

-- Find orphaned posts (no matching user)
SELECT posts.* FROM posts
LEFT JOIN users ON posts.user_id = users.id
WHERE users.id IS NULL;

-- Delete orphaned records
DELETE FROM posts WHERE user_id NOT IN (SELECT id FROM users);

-- Or set to NULL if column allows
UPDATE posts SET user_id = NULL
WHERE user_id NOT IN (SELECT id FROM users);

Related Errors

UNIQUE constraint failed - Duplicate value
NOT NULL constraint failed - Missing required value