Rails migration file to create table and add unique index on two fields seems being ignored during migration

Tuesday, April 24, 2018

Rails migration file to create table and add unique index on two fields seems being ignored during migration

Answer

I'm totally stuck here! I want to add a unique index so that no two records in an association table can have the same combination of user_id and course_id.

I have created the following migration file in rails:

class CreateSignups < ActiveRecord::Migration
  def change

    create_table :signups do |t|

      t.integer :course_id

      t.integer :user_id

      t.timestamps null: false
    end

    add_index :signups, :course_id
    add_index :signups, :user_id
    add_index :signups, [:course_id, :user_id], unique: true

  end

end

...but for some reason, the unique 'course_id & user_id' is not being represented in the schema.rb and using the rails console the system lets me manually create a multiple records where the course_id and the user_id are exactly the same.

The signups table is managed by the Signup model and has an integer primary key called 'id'. The course and user Model & database table follow standard rails naming convention.

Can anyone see why it's the unique criteria is not being understood in this migration?

Thanks in advance!

Answer

Did you already run your migration once (to create table) and then add the index? You can check it by doing following:

bundle exec rails dbconsole
select * from schema_migrations;

If your migration version is already recorded in the schema_migrations table, and doing rake db:migrate won't run it again.

To add indexes, you have 2 options:

Rollback the migration for CreateSignups. This will drop the current table and then doing rake db:migrate will re-create the table with indexes.

If you don't want to loose the data in previous step, then you'll have to explicitly create indexes in MySQL from rails dbconsole.

Blog

Tuesday, April 24, 2018