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?

You need to remove the index.

Pavan
– Pavan

2015-11-03 09:38:27 +00:00
Commented Nov 3, 2015 at 9:38 — Pavan
– Pavan, Commented Nov 3, 2015 at 9:38

Stéphane Bruckert · Accepted Answer · 2018-11-30 11:50:27Z

70

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

edited Nov 30, 2018 at 11:50

Stéphane Bruckert

23.1k15 gold badges102 silver badges136 bronze badges

answered Nov 3, 2015 at 9:40

dthal

1,0231 gold badge8 silver badges15 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jason Swett Over a year ago

This no longer works. What works now is: remove_index :profile_pictures, name: 'picture_id'

Andreas Baumgart Over a year ago

Beware, a rollback of this migration won't restore the UNIQUE constraint.

Andreas Baumgart · Accepted Answer · 2020-05-20 23:00:20Z

18

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

answered May 20, 2020 at 23:00

Andreas Baumgart

2,7771 gold badge26 silver badges20 bronze badges

1 Comment

Daniel Over a year ago

I've accepted this as the new answer

Bryan Oemar · Accepted Answer · 2015-11-03 09:41:06Z

11

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.

edited Nov 3, 2015 at 9:41

answered Nov 3, 2015 at 9:31

Bryan Oemar

9267 silver badges16 bronze badges

Comments

Rocket Appliances · Accepted Answer · 2022-05-05 20:44:03Z

2

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

answered May 5, 2022 at 20:44

Rocket Appliances

3774 silver badges14 bronze badges

Collectives™ on Stack Overflow

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

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related