Schema migration rollback fails on foreign key constraint violation

You're running a database migration rollback, maybe with rake db:rollback, manage.py migrate app 0001, or a raw SQL script, and you see this:

ERROR: cannot drop table job_assignments because other objects depend on it
DETAIL: constraint job_logs_job_assignment_fkey on table job_logs depends on table job_assignments

The rollback tried to drop job_assignments but a foreign key in job_logs points to it. The database says no. This happens when your migration rollback is ordered wrong — it's trying to undo a table before undoing the table that references it.

Why this happens

Migration frameworks like Rails ActiveRecord or Django's built-in migrations usually handle rollback order for you. They generate the down method by reversing the up method. But here's the catch: if you wrote custom SQL in the migration (or used a gem that does), the down might not respect dependency order.

For example, in Rails:

class CreateJobAssignments < ActiveRecord::Migration[7.0]
  def up
    create_table :job_assignments do |t|
      t.string :name
      t.timestamps
    end
    # some custom SQL that creates job_logs referencing job_assignments
    execute 'CREATE TABLE job_logs (id serial, job_assignment_id integer REFERENCES job_assignments(id))'
  end

  def down
    drop_table :job_assignments
  end
end

The down drops job_assignments but never drops job_logs first. The database can't drop a table that's being referenced by a foreign key.

Another common trigger: you have a circular dependency. Table A references B, and B references A. Neither can be dropped without the other going first. This usually means your schema design is wrong, but you're stuck trying to roll back to fix it.

The fix

1. Identify the dependency chain. Run this SQL to see the full dependency tree for the table you're trying to drop:

SELECT
    conname AS constraint_name,
    conrelid::regclass AS referencing_table,
    confrelid::regclass AS referenced_table
FROM
    pg_constraint
WHERE
    contype = 'f'
    AND conrelid = 'job_assignments'::regclass
    OR confrelid = 'job_assignments'::regclass;

This gives you the order: drop tables that reference job_assignments before dropping job_assignments itself.

2. If you're using a migration framework, edit the migration file to add the missing reverse operations. For Rails, modify the down method:

def down
  drop_table :job_logs
  drop_table :job_assignments
end

For Django, make sure your migration operations are in the correct reverse order. Django migrations are reversible by default with CreateModel, but if you used RunSQL, you need to provide reverse_sql.

migrations.RunSQL(
    sql="CREATE TABLE job_logs (...)",
    reverse_sql="DROP TABLE IF EXISTS job_logs CASCADE"
)

3. If you're running raw SQL rollback scripts, wrap the drop in a transaction that drops referencing constraints first:

BEGIN;

-- Drop foreign key constraints on referencing tables
ALTER TABLE job_logs DROP CONSTRAINT job_logs_job_assignment_fkey;

-- Now you can drop the referenced table
DROP TABLE job_assignments;

-- Drop the referencing table if needed (or keep it without the FK)
DROP TABLE job_logs;

COMMIT;

Using CASCADE is a blunt instrument — it drops the referencing tables silently. Use it only if you're sure you want to nuke everything:

DROP TABLE job_assignments CASCADE;

4. For circular dependencies, you can't drop both at once with CASCADE — PostgreSQL detects the cycle and refuses. You need to break the cycle temporarily:

BEGIN;

ALTER TABLE table_a DROP CONSTRAINT fk_a_to_b;
DROP TABLE table_b;
DROP TABLE table_a;

COMMIT;

Then recreate the tables in the correct order (A first, then B with the FK) when you rerun the forward migration.

What to check if it still fails

• Check for triggers or views that reference the table. Use pg_depend to find them:

SELECT DISTINCT
    dependent.relname AS dependent_object,
    CASE dependent.relkind
        WHEN 'v' THEN 'view'
        WHEN 'm' THEN 'materialized view'
        WHEN 'f' THEN 'foreign table'
        WHEN 'S' THEN 'sequence'
        ELSE 'unknown'
    END AS object_type
FROM pg_depend
JOIN pg_class referenced ON referenced.oid = pg_depend.refobjid
JOIN pg_class dependent ON dependent.oid = pg_depend.objid
WHERE referenced.relname = 'job_assignments';

• Make sure you're not in a transaction that's holding locks. If you're running this from a migration script, that script might have an implicit transaction. Check with SELECT * FROM pg_locks WHERE relation::regclass = 'job_assignments'::regclass;.

• Verify the migration version. If your migration framework tracks version numbers, make sure you're rolling back to the correct version. In Rails, rake db:migrate:status shows the current state. In Django, python manage.py showmigrations lists applied vs. unapplied.

One last thing: if you're in production and you get this, stop. Don't force-drop with CASCADE without understanding the full dependency tree. You could lose data in tables you didn't mean to touch. Instead, create a new migration that explicitly drops constraints in the right order, then run the rollback. That's safer and gives you time to review what happened.