How can I remove a unique constraint from a database column in Rails?

Question

I created a table using the following migration:

class CreateProfilePictures < ActiveRecord::Migration
  def change
    create_table :profile_pictures do |t|
      t.integer :user_id, null: false
      t.integer :picture_id, null: false
      t.timestamps null: false
    end

    add_index :profile_pictures, :user_id, unique: true
    add_index :profile_pictures, :picture_id, unique: true
  end
end

I tried to remove the constraint with the following:

class FixProfilePic < ActiveRecord::Migration
  def change
    change_column :profile_pictures, :picture_id, :integer, unique: false
  end
end

I still get a unique constraint violation error if I try to use the same picture_id in more than one place. What is the proper way to remove the uniqueness constraint from picture_id?

Answer 1

There is a problem with the accepted answer: Rollbacks don't work correctly as the unique index is not restored.

You could try this instead:

reversible do |dir|
  dir.up do
    remove_index :profile_pictures, :picture_id
    add_index :profile_pictures, :picture_id
  end

  dir.down do
    remove_index :profile_pictures, :picture_id
    add_index :profile_pictures, :picture_id, unique: true
  end
end