migration to add index when column is already exists in db rails?

Question

how to add index when column is already exists in my db?

when i create migration like this :

class AddIndexToUsers < ActiveRecord::Migration
  def change
    remove_column :users, :id_number if index_exists?(:id_number)
    add_column :users, :id_number, :string
    add_index :users, :id_number, unique: true
  end
end

i get error like this when create object with same id_number :

SQLite3::ConstraintException: column id_number is not unique: INSERT INTO "users" ("bank_account", "birth_date", "confirmed", "confirmed_at", "created_at", "credit_card_id", "email", "encrypted_password", "facebook_id", "google_id", "id_issuing_country", "id_number", "last_name", "name", "updated_at") VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)

You've added a unique index - what did you think would happen when trying to insert duplicate id_number? — Frederick Cheung, Aug 22 '14 at 06:03

score 0 · Answer 1 · answered Aug 22 '14 at 09:50

class AddIndexToUsers < ActiveRecord::Migration
 def change
  remove_index :users, :id_number if index_exists?(:id_number)
  add_index :users, :id_number, unique: true
  end
end

This wiil fail if your already duplicate id_number entries in the :users table. You will have to remove the duplicates first if any

score -1 · Answer 2 · edited Aug 22 '14 at 06:32

Think about your question very carefully...Why do you want to add something that already exists? If you want to generate a model in a relational database and not have an ID to it, you need to pass in :id => false

create_table(:categories_suppliers, id: false) do |t|
  t.column :category_id, :integer
  t.column :supplier_id, :integer
end

If you're mistaken, and your trying to ensure there is an ID to your model, you can test by going to your terminal and typing rails c

@users = User.all
@users.first.id

To validate this, you will need to actually have some data in your table. Of course, you'll know, because typing @users into your terminal will return an empty array.

migration to add index when column is already exists in db rails?

2 Answers2