5

Rails 5.1.0 introduces the bigint primary keys and foreign keys.

All new tables will have bigint pk and creating a reference migration to an old table will not work since the old pk is a normal int.

Using change_column _, :id,:bigint just errors with a foreign key is pointing towards it, not to mention all the manual labour of finding all the tables and which has which key that needs to be modified.

How do I migrate my production database all my tables to use bigint pk and fk's?

Ofcourse since it's production rails db:drop rails db:setup is not an option.

Iaan Krynauw
  • 311
  • 3
  • 16
  • In CHANGELOG I found that only primary keys became BIGINT. Could you please find a link about foreign keys behavior changes? – Ilya May 04 '17 at 12:21
  • I tried to create a table referencing to an old table and it fails because the old table has a small int pk. Changing the migration from [5.1] to [5.0] made it work except no bigints. So it didn't say change but it impacts the fk's. – Iaan Krynauw May 04 '17 at 12:37

1 Answers1

1

I facing the same problem. Temporary remove fk's should work. The goal is to change all primary keys from int to bigint.

class ChangeForeignKeysToBigInt < ActiveRecord::Migration[5.1]
  def change

    remove_foreign_key "event_users", "events"

    change_column :event_users, :event_id, :bigint
    change_column :events, :id, :bigint

    add_foreign_key "event_users", "events"

  end
end

you have to do that with all your tables and foreign key columns maybe handle indices the same way, i dont tested that

grohmio
  • 11
  • 1
  • Thanks yeah this will probably work, but I have a lot of tables is there no way of doing it all at once? Or disabling the bigint situation? – Iaan Krynauw May 13 '17 at 08:36
  • You can automate that with all the primary keys, the bigger problem are the foreign key columns that point to a primary key, its possible to automate that but more difficult. Just write a loop over all tables, change the primary_key, change also columns with "_id" inside and with integer as datatype, that should work – grohmio May 13 '17 at 21:57
  • I have a lot of tables I can probably go and use my sublime text skills and get everything but I don't want to do that much manual labour to upgrade. Call me lazy but I feel rails should have introduced a strategy to do this. – Iaan Krynauw May 18 '17 at 11:13
  • 1
    I wrote a migration that adopted this approach for all tables in my schema. When it's run it changes all :id columns into bigints but loses the info that they should be primary keys. Instead the create_table definitions have default: nil and nothing about autoincrementing for the :id columns. Has anyone else run into this problem? – Barry Jan 12 '18 at 17:55