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 Nov 03 '15 at 09:38 — Pavan, Nov 03 '15 at 09:38

score 69 · Answer 1 · edited Nov 30 '18 at 11:50

69

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 '18 at 11:50

Stéphane Bruckert

21,706
14
92
130

answered Nov 03 '15 at 09:40

dthal

1,003
1
8
15

1

This no longer works. What works now is: `remove_index :profile_pictures, name: 'picture_id'` – Jason Swett Jan 06 '20 at 15:06
2

Beware, a rollback of this migration won't restore the UNIQUE constraint. – Andreas Baumgart May 20 '20 at 22:55

score 15 · Accepted Answer · answered May 20 '20 at 23:00

15

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 '20 at 23:00

Andreas Baumgart

2,647
1
25
20

2

I've accepted this as the new answer – Daniel Jan 25 '22 at 02:41

Bryan Oemar · Answer 3 · 2015-11-03T09:41:06.327

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 03 '15 at 09:41

answered Nov 03 '15 at 09:31

Bryan Oemar

916
7
16

score 1 · Answer 4 · answered May 05 '22 at 20:44

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

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

4 Answers4

Linked