cannot drop table: foreign key constraint

This error occurs when you try to drop a table that other tables reference via foreign keys. Learn how to safely drop tables with dependencies.

The cannot drop table error occurs when foreign key constraints prevent dropping a referenced table.

Understanding the Error

Error: FOREIGN KEY constraint failed (when dropping table)

Another table has foreign keys pointing to the table you're trying to drop.

Common Causes

1. Referenced Table

SQL

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

-- Fails: posts references users
DROP TABLE users;

2. Hidden References

You might not realize a table is referenced.

How to Fix It

Solution 1: Drop Dependent Tables First

SQL

-- Drop in reverse order of creation
DROP TABLE posts;    -- References users
DROP TABLE users;    -- Now safe to drop

Solution 2: Find All References

SQL

-- Find tables that reference 'users'
SELECT
  m.name AS referencing_table,
  p."from",
  p."to",
  p."table" AS referenced_table
FROM sqlite_master m
JOIN pragma_foreign_key_list(m.name) p
WHERE p."table" = 'users';

Solution 3: Disable Foreign Keys

SQL

-- Temporarily disable foreign key enforcement
PRAGMA foreign_keys = OFF;

DROP TABLE users;

PRAGMA foreign_keys = ON;

Warning: This leaves orphaned references in dependent tables.

Solution 4: Use CASCADE (Recreate Tables)

SQLite doesn't support DROP TABLE CASCADE, but you can recreate:

SQL

-- Remove the foreign key constraint first
CREATE TABLE posts_new (
  id INTEGER PRIMARY KEY,
  user_id INTEGER  -- No foreign key
);
INSERT INTO posts_new SELECT * FROM posts;
DROP TABLE posts;
ALTER TABLE posts_new RENAME TO posts;

-- Now you can drop users
DROP TABLE users;

Solution 5: Delete Referencing Data First

SQL

-- Remove references first
DELETE FROM posts WHERE user_id IN (SELECT id FROM users);

-- Or set to NULL if allowed
UPDATE posts SET user_id = NULL;

-- Now drop works
DROP TABLE users;

Solution 6: Drop and Recreate Schema

For complete reset:

SQL

PRAGMA foreign_keys = OFF;

-- Drop all tables
DROP TABLE IF EXISTS posts;
DROP TABLE IF EXISTS comments;
DROP TABLE IF EXISTS users;

PRAGMA foreign_keys = ON;

-- Recreate schema
CREATE TABLE users (...);
CREATE TABLE posts (...);

Checking Foreign Key Dependencies

SQL

-- List all foreign keys in database
SELECT
  m.name AS table_name,
  p.id,
  p."table" AS references_table,
  p."from" AS from_column,
  p."to" AS to_column
FROM sqlite_master m
JOIN pragma_foreign_key_list(m.name) p
WHERE m.type = 'table';

JAVASCRIPT

// Programmatic check
function getDependencies(db, tableName) {
  const tables = db.prepare(`
    SELECT name FROM sqlite_master WHERE type='table'
  `).all();

  const dependents = [];
  for (const { name } of tables) {
    const fks = db.pragma(`foreign_key_list(${name})`);
    for (const fk of fks) {
      if (fk.table === tableName) {
        dependents.push({ table: name, column: fk.from });
      }
    }
  }
  return dependents;
}

Best Practices

Document table dependencies in your schema
Create a drop order for teardown scripts
Use migrations that handle dependencies
Check dependencies before dropping
Consider soft deletes instead of drops