Fixing PostgreSQL 'cannot drop table because other objects depend on it' When Dropping Tables with Foreign Key Constraints

The Error

You run a seemingly simple DROP TABLE users; and PostgreSQL slams the brakes:

ERROR: cannot drop table users because other objects depend on it
DETAIL: constraint orders_user_id_fkey on table orders depends on table users
HINT: Use DROP ... CASCADE to drop the dependent objects too.

This isn't PostgreSQL being difficult — it's saving you from a data integrity disaster. The orders table has a foreign key pointing at users. If users disappeared, those orders rows would reference nothing. PostgreSQL blocks the drop and waits for you to decide what happens to the dependents.

Map the Blast Radius First

Don't guess. Run this query to see every object referencing your table before touching anything:

SELECT
    tc.table_name AS dependent_table,
    tc.constraint_name,
    tc.constraint_type,
    kcu.column_name,
    ccu.table_name AS referenced_table
FROM information_schema.table_constraints AS tc
JOIN information_schema.key_column_usage AS kcu
    ON tc.constraint_name = kcu.constraint_name
    AND tc.table_schema = kcu.table_schema
JOIN information_schema.constraint_column_usage AS ccu
    ON ccu.constraint_name = tc.constraint_name
    AND ccu.table_schema = tc.table_schema
WHERE tc.constraint_type = 'FOREIGN KEY'
  AND ccu.table_name = 'users';

Sample output:

 dependent_table | constraint_name       | constraint_type | column_name | referenced_table
-----------------+-----------------------+-----------------+-------------+------------------
 orders          | orders_user_id_fkey   | FOREIGN KEY     | user_id     | users
 profiles        | profiles_user_id_fkey | FOREIGN KEY     | user_id     | users
 sessions        | sessions_user_id_fkey | FOREIGN KEY     | user_id     | users

Three tables. Now you know exactly what's in the way.

Option 1: DROP TABLE ... CASCADE (Fastest, Most Dangerous)

Working in dev or test and you don't care about the dependents? One line does it:

DROP TABLE users CASCADE;

CASCADE drops users and automatically removes every foreign key constraint that references it. The dependent tables — orders, profiles, sessions — survive. Their data stays intact. Only the FK constraints pointing at users get removed.

Production warning: Run the dependency query above first. A schema with 15–20 dependent tables can cascade through relationships you forgot existed, silently stripping constraints that other queries rely on. Check before you run.

Option 2: Drop the Foreign Key Constraints First (Safer)

This is the surgical approach. Remove only the constraints blocking you — the dependent tables and their data are untouched:

-- Drop each foreign key constraint explicitly
ALTER TABLE orders DROP CONSTRAINT orders_user_id_fkey;
ALTER TABLE profiles DROP CONSTRAINT profiles_user_id_fkey;
ALTER TABLE sessions DROP CONSTRAINT sessions_user_id_fkey;

-- Now the drop succeeds
DROP TABLE users;

Reach for this when you're restructuring a schema and need to preserve the shape of the dependent tables. It's more verbose, but you know exactly what changed.

Option 3: Drop Everything in the Right Order

Rolling back a migration? Tear down child tables before the parent:

-- Dependents first, then the parent
DROP TABLE sessions;
DROP TABLE profiles;
DROP TABLE orders;
DROP TABLE users;

Wrap it in a transaction to make it atomic — if any step fails, nothing gets dropped:

BEGIN;
  DROP TABLE sessions;
  DROP TABLE profiles;
  DROP TABLE orders;
  DROP TABLE users;
COMMIT;

Option 4: Nuke an Entire Schema

All these tables live in the same schema and you want a clean slate? Skip the individual drops entirely:

DROP SCHEMA public CASCADE;
CREATE SCHEMA public;

Everything in the schema is gone. Reserve this for dev/test environments or full resets — it's not reversible without a backup.

Verify the Fix

After dropping, confirm the table and its constraints are actually gone:

-- Check table no longer exists
SELECT table_name
FROM information_schema.tables
WHERE table_name = 'users';
-- Should return 0 rows

-- Check no orphaned FK constraints remain pointing to 'users'
SELECT constraint_name, table_name
FROM information_schema.table_constraints
WHERE constraint_name LIKE '%user%'
  AND constraint_type = 'FOREIGN KEY';
-- Should return 0 rows (or only unrelated constraints)

Went with Option 2? Make sure the dependent tables kept their data:

SELECT COUNT(*) FROM orders;    -- data still there
SELECT COUNT(*) FROM profiles;  -- data still there

Tips to Avoid This Next Time

• Run the dependency query before any schema change in production. Make it a habit, not an afterthought — a 3-second query beats a 3-hour incident.
• Use DROP TABLE IF EXISTS ... CASCADE in migration scripts. It won't error on a fresh DB, and you don't have to manually track constraint names across environments.
• Name your constraints explicitly. CONSTRAINT orders_user_id_fkey FOREIGN KEY (user_id) REFERENCES users(id) is far easier to target in an ALTER TABLE DROP CONSTRAINT than auto-generated names like orders_user_id_fkey1.
• Test migrations on staging first. CASCADE behavior on a production schema with 20 dependent tables produces surprises that a staging run would have caught.
• Use the GUI when the graph is complex. In pgAdmin or DBeaver, right-click the table → "Dependencies" to see the full dependency tree visually before running any DDL.