PG::DependentObjectsStillExist: ERROR: cannot drop table reports because other objects depend on it

Question

to give a bit of background. I’m a newbie in Ruby on Rails and I’m making a review website where users can report a review. I played around with having a ‘reportable’ polymorphic association and then reports table. Later I deleted the ‘reports’ table and created another reports table. The earlier migration was not allowing to create a new reports table migration, so I added ‘1’ next to the name of the migration earlier migration.

20180311071357_create_reports_1.rb
class CreateReports1 < ActiveRecord::Migration[5.0]
  def change
    create_table :reports do |t|
      t.string :reason
      t.text :description
      t.string :email
      t.belongs_to :reportable, polymorphic: true
      t.timestamps
    end
  end
end

Fast forward to the present time. I didn’t do a production commit to Heroku for some time and I was just building the app. When I pushed to heroku, I’m getting the following error-

heroku run rake db:migrate --trace

Running rake db:migrate --trace on ⬢ housereview... up, run.6689 (Free)
** Invoke db:migrate (first_time)
** Invoke environment (first_time)
** Execute environment
** Invoke db:load_config (first_time)
** Execute db:load_config
** Execute db:migrate
   (4.0ms)  SELECT pg_try_advisory_lock(2386658352535867725);
  ActiveRecord::SchemaMigration Load (4.6ms)  SELECT "schema_migrations".* FROM "schema_migrations"

Migrating to DeleteReports1 (20180318091834)
   (1.2ms)  BEGIN
== 20180318091834 DeleteReports1: migrating ===================================
-- drop_table(:reports)
   (2.8ms)  DROP TABLE "reports"
   (2.6ms)  ROLLBACK
   (2.3ms)  SELECT pg_advisory_unlock(2386658352535867725)

rake aborted!

StandardError: An error has occurred, this and all later migrations canceled:

PG::DependentObjectsStillExist: ERROR:  cannot drop table reports because other objects depend on it

DETAIL:  constraint fk_rails_cfc8432c20 on table notes depends on table reports

HINT:  Use DROP ... CASCADE to drop the dependent objects too.
: DROP TABLE "reports"
...
/app/bin/bundle:3:in `load'
/app/bin/bundle:3:in `<main>'

Caused by:
ActiveRecord::StatementInvalid: PG::DependentObjectsStillExist: ERROR:  cannot drop table reports because other objects depend on it

DETAIL:  constraint fk_rails_cfc8432c20 on table notes depends on table reports

HINT:  Use DROP ... CASCADE to drop the dependent objects too.
: DROP TABLE "reports"
/app/vendor/bundle/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'

.......
/app/bin/bundle:3:in `load'
/app/bin/bundle:3:in `<main>'

Caused by:
PG::DependentObjectsStillExist: ERROR:  cannot drop table reports because other objects depend on it

DETAIL:  constraint fk_rails_cfc8432c20 on table notes depends on table reports

HINT:  Use DROP ... CASCADE to drop the dependent objects too.
/app/vendor/bundle/ruby/2.3.0/gems/activerecord-5.0.6/lib/active_record/connection_adapters/postgresql/database_statements.rb:98:in `async_exec'

This is my schema-

ActiveRecord::Schema.define(version: 20180407122611) do

  create_table "locations", force: :cascade do |t|
    t.string "address"
    t.string "street_number"
    t.string "locality"
    t.string "postal_code"
    t.string "country"
    t.string "route"
    t.string "state"
  end

  create_table "notes", force: :cascade do |t|
    t.text     "description"
    t.integer  "report_id"
    t.datetime "created_at",  null: false
    t.datetime "updated_at",  null: false
    t.index ["report_id"], name: "index_notes_on_report_id"
  end

  create_table "reports", force: :cascade do |t|
    t.string   "reason"
    t.text     "description"
    t.integer  "user_id"
    t.integer  "review_id"
    t.datetime "created_at",   null: false
    t.datetime "updated_at",   null: false
    t.datetime "completed_at"
    t.index ["review_id"], name: "index_reports_on_review_id"
  end

  create_table "reviews", force: :cascade do |t|
    t.string   "title"
    t.string   "duration"
    t.text     "positive"
    t.text     "negative"
    t.integer  "location_id"
    t.integer  "user_id"
    t.integer  "rating_safety"
    t.integer  "rating_neighbour"
    t.integer  "rating_owner"
    t.integer  "rating_school"
    t.datetime "created_at"
    t.datetime "updated_at"
    t.integer  "rating_overall"
    t.boolean  "blocked"
    t.index ["location_id"], name: "index_reviews_on_location_id"
  end

  create_table "users", force: :cascade do |t|
    t.string   "email",                  default: "",    null: false
    t.string   "encrypted_password",     default: "",    null: false
    t.string   "reset_password_token"
    t.datetime "reset_password_sent_at"
    t.datetime "remember_created_at"
    t.integer  "sign_in_count",          default: 0,     null: false
    t.datetime "current_sign_in_at"
    t.datetime "last_sign_in_at"
    t.string   "current_sign_in_ip"
    t.string   "last_sign_in_ip"
    t.string   "confirmation_token"
    t.datetime "confirmed_at"
    t.datetime "confirmation_sent_at"
    t.string   "unconfirmed_email"
    t.datetime "created_at",                             null: false
    t.datetime "updated_at",                             null: false
    t.boolean  "admin",                  default: false
    t.string   "first_name"
    t.string   "last_name"
    t.string   "locality"
    t.string   "state"
    t.index ["email"], name: "index_users_on_email", unique: true
    t.index ["reset_password_token"], name: "index_users_on_reset_password_token", unique: true
  end

I tried (almost) everything to fix the issue. The following things I’ve tried so far- - Deleted the production app and then restore pgbackup - Delete the production app and then tried to do a fresh ‘git push heroku master’ and then ‘heroku run rake db:migrate’ - Tried drop table command and then pushing code again and rake db:migrate - Tried ‘DROP TABLE if exists reports cascade;’ in pgsql

All the above had no luck. The app works perfect in dev (SQLite) but heroku is not letting me rake db:migrate.

Thanks in advance!

Answer 1

Notes table contains report_id as foreign key so you did not able to drop reports table. You can do by using dependent: :destroy In report.rb file change below line

has_many :notes

to

has_many :notes, dependent: :destroy 

Answer 2

Do you still have the migration that created the notes table around? I would guess that the create_table :notes in that migration won't exactly match what you're seeing in your db/schema.rb.

No matter, your problem is that the notes.report_id references reports.id via a foreign key inside the database. This foreign key was probably created by a t.references :report, foreign_key: true in a migration to ensure referential integrity (i.e. the database will ensure that any value in notes.report_id references something in the reports table). This foreign key constraint is preventing you from dropping the reports table as that would violate the constraint on notes.report_id.

I don't think you actually want to delete the reports table at all. Instead, you want to make the reports table at Heroku match what schema.rb says. Migrations aren't meant to last forever so go ahead and delete the offending migrations and write a new one that brings the reports table at Heroku in sync with what schema.rb says.

Once you have this mess cleaned up, there are two things you'll want to do:

1. Never rename migrations again. Delete old migrations and add new ones now and then. If you want to keep an old one around but use its name for something else then put a suffix on the new migration but really, there's no reason to keep old migrations around forever.

2. Install PostgreSQL in your local development environment. You should always be developing, testing, and deploying with the same stack. There are so many differences between databases that ActiveRecord won't protect you from that using different databases in the three environments is madness and little more than a short cut to pain and suffering.