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

Question

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 1

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:

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

2. 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.