Ruby on Rails: Datatype foreign key

Question

I have the following tables:

• Route(ID, Name) with Primary Key ID
• Stop(ID, Name) with Primary Key ID
• Mapping(Route_ID, Stop_ID)

The IDs in Route and Stop are of type BIGINT(20) in my Mysql-DB. The migration fails because using this:

class CreateMappings < ActiveRecord::Migration
def change
  create_table :mappings do |t|
    t.references :route, index: true, foreign_key: true
    t.references :stop, index: true, foreign_key: true

    t.timestamps null: false
  end
  end
end

Creates a table Mappings with route_id and stop_id but datatype INT(11). This is not compatible with the BIGINT(20). How can I fix this? Any ideas? The creation of the foreign keys fails.

Error Messages

This is a section of the output of rake db:migrate --trace:

** Invoke db:migrate (first_time) ** Invoke environment (first_time) ** Execute environment ** Invoke db:load_config (first_time) ** Execute db:load_config ** Execute db:migrate == 20151227194101 CreateMappings: migrating =================================== -- create_table(:mappings) rake aborted! StandardError: An error has occurred, all later migrations canceled:

Mysql2::Error: Cannot add foreign key constraint: ALTER TABLE mappings ADD CONSTRAINT fk_rails_1b9f715271 FOREIGN KEY (route_id) REFERENCES routes (id)

When I try to execute the above SQL statement (ALTER TABLE mappings...) using a MySql-Client, I get this error:

Failed to add the foreign key constaint. MIssing index for constraint 'fk_rails_1b9f715271' in the referenced table 'routes'.

Answer 1

The references method takes a type option if you don't want the added column to be an integer, for example

t.references :route, type: :bigint, index: true, foreign_key: true

Answer 2

Have you tried this form?

class CreateMappings < ActiveRecord::Migration
  def change
    create_table :mappings do |t|
      t.references :route
      t.references :stop

      t.timestamps null: false
    end
  end
  add_index(:mappings, :route)
  add_index(:mappings, :stop)
end