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

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?

sqlruby-on-railsrails-migrations


Solution 1:[1]

You must remove your index with:

remove_index :profile_pictures, :picture_id

and add it again with:

add_index :profile_pictures, :picture_id

ActiveRecord::Migration

Solution 2:[2]

add_index :profile_pictures, :picture_id, unique: true

So update your index to:

  remove_index :profile_pictures, :picture_id
  add_index :profile_pictures, :picture_id

I'm guessing this is it.

Solution 3:[3]

For the migration to be reversible, you now (Rails 6+) need to use the :column option.

Adding unique: true also ensures rolling back the migration will recreate the index with the uniqueness constraint (barring problematic data being inserted in the meantime).

  remove_index :profile_pictures, column: :picture_id, unique: true
  add_index :profile_pictures, :picture_id

One more thing to note: if your table is large, then creating a new index on it could lock writes to it for a long time. Consider using algorithm: :concurrently if you use Postgres

Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source
Solution 1 Stéphane Bruckert
Solution 2
Solution 3 Rocket Appliances

Related Questions