Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Rails: Adding an index after adding column

People also ask

What is ADD index in rails?

An index is used to speed up the performance of queries on a database. Rails allows us to create index on a database column by means of a migration. By default, the sort order for the index is ascending. But consider the case where we are fetching reports from the database.

How do I add a reference column in rails?

When you already have users and uploads tables and wish to add a new relationship between them. Then, run the migration using rake db:migrate . This migration will take care of adding a new column named user_id to uploads table (referencing id column in users table), PLUS it will also add an index on the new column.

What is index in active record?

Indexes allow your database to quickly find and sort records in a table by keeping pre-organised copies of your data. Conceptually, they're like the index of a book.

What is index true?

index: true adds a database index to the referenced column. For example, if creating a :products table: create_table :products do |t| t.references :user, index: true end. That will create a non-unique index on the user_id column in the products table named index_products_on_user_id .


You can run another migration, just for the index:

class AddIndexToTable < ActiveRecord::Migration
  def change
    add_index :table, :user_id
  end
end

If you need to create a user_id then it would be a reasonable assumption that you are referencing a user table. In which case the migration shall be:

rails generate migration AddUserRefToProducts user:references

This command will generate the following migration:

class AddUserRefToProducts < ActiveRecord::Migration
  def change
    add_reference :user, :product, index: true
  end
end

After running rake db:migrate both a user_id column and an index will be added to the products table.

In case you just need to add an index to an existing column, e.g. name of a user table, the following technique may be helpful:

rails generate migration AddIndexToUsers name:string:index will generate the following migration:

class AddIndexToUsers < ActiveRecord::Migration
  def change
    add_column :users, :name, :string
    add_index :users, :name
  end
end

Delete add_column line and run the migration.

In the case described you could have issued rails generate migration AddIndexIdToTable index_id:integer:index command and then delete add_column line from the generated migration. But I'd rather recommended to undo the initial migration and add reference instead:

rails generate migration RemoveUserIdFromProducts user_id:integer
rails generate migration AddUserRefToProducts user:references

Add in the generated migration after creating the column the following (example)

add_index :photographers, :email, :unique => true

For references you can call

rails generate migration AddUserIdColumnToTable user:references

If in the future you need to add a general index you can launch this

rails g migration AddOrdinationNumberToTable ordination_number:integer:index

Generate code:

class AddOrdinationNumberToTable < ActiveRecord::Migration
  def change
   add_column :tables, :ordination_number, :integer
   add_index :tables, :ordination_number, unique: true
  end
end

You can use this, just think Job is the name of the model to which you are adding index cader_id:

class AddCaderIdToJob < ActiveRecord::Migration[5.2]
  def change
    change_table :jobs do |t|
      t.integer :cader_id
      t.index :cader_id
    end
  end
end

For those who are using postgresql db and facing error

StandardError: An error has occurred, this and all later migrations canceled:

=== Dangerous operation detected #strong_migrations ===

Adding an index non-concurrently blocks writes

please refer this article

example:

class AddAncestryToWasteCodes < ActiveRecord::Migration[6.0]
  disable_ddl_transaction!

  def change
    add_column :waste_codes, :ancestry, :string
    add_index :waste_codes, :ancestry, algorithm: :concurrently
  end
end