remove_reference and down migration of add_reference reports no foreign key

Question

Migrating up this migration

def change
    remove_reference :order_items, :order, foreign_key: true
end

or migrating down this migration

def change
    add_reference :order_items, :order, foreign_key: true
end

is throwing this error:

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

Table 'order_items' has no foreign key for {:to_table=>"orders"}
G:/Rails/learn_rails/db/migrate/20170222035809_move_order_items_under_restaurant_orders.rb:3:in `change'
G:/Rails/learn_rails/bin/rails:4:in `require'
G:/Rails/learn_rails/bin/rails:4:in `<top (required)>'
-e:1:in `load'
-e:1:in `<main>'
ArgumentError: Table 'order_items' has no foreign key for {:to_table=>"orders"}
G:/Rails/learn_rails/db/migrate/20170222035809_move_order_items_under_restaurant_orders.rb:3:in `change'
G:/Rails/learn_rails/bin/rails:4:in `require'
G:/Rails/learn_rails/bin/rails:4:in `<top (required)>'
-e:1:in `load'
-e:1:in `<main>'
Tasks: TOP => db:migrate
(See full trace by running task with --trace)

My order_items table does have order_id column (and it is indexed) and I do have orders table, but then why isn't rails finding the column order_id and dropping it?

I solve it temporarily by

remove_index :order_items, :order_id
remove_column :order_items, : order_id

but what do I do if I have to migrate down add_reference?

Answer 1

order_items table had order_id column and and index index_order_items_on_order_id.

BUT it did not have the FOREIGN KEY as the error clearly stated.

Do a crosscheck in the db if that exists like as shown (there are many tools available). Foreign keys are the ones that are in the form fk_rails_hash

In my case, the foreign keys were lost during an improper export dump and when I imported that sql_dump and migrated my data to this database, they were not present, though the indices and columns were preserved.

Solutions:

• Remove columns and add references the right way

  remove_index :order_items, :order_id
  remove_column :order_items, :order_id
  add_reference :order_items, :order, foreign_key: true
  
  # or add only the foreign the key constraint
  # i'm afraid it I would miss anything else doing so
  

• Best Solution if there are so many foreign keys missing

  # instead of whole sql_dump
  # export only the data
  
  rails db:migrate:reset
  
  # import only the data making sure the insert
  # happens in the right order as it may violate
  # foreign_key constraints that are newly added