Rails error: Index name '...' on table '...' already exists while running rails db:migrate

Question

So I'm working with a colleague who added some additional migration files and per normal procedure once I pulled their version I ran rails db:migrate. I end up getting the following errors:

 Index name 'index_authorizations_on_user_id' on table 'authorizations' already exists

 ArgumentError: Index name 'index_authorizations_on_user_id' on table 'authorizations' already exists

So I went and checked the schema and the table is already present. So why is it failing? Typically in the past it only generates new table entries/updates when doing a migration so why isn't it just ignoring it?

I've tried doing a rollback and get: This migration uses remove_columns, which is not automatically reversible.

I've tried doing bin/rails db:migrate RAILS_ENV=development and I get the same errors.

I've done a db:reset, db:drop, and it all comes back to an issue with pending migrations that I cannot get to run. What am I doing wrong?

They added the following migration: 20171024074328_create_authorizations.rb

 class CreateAuthorizations < ActiveRecord::Migration[5.1]
  def change
    create_table :authorizations do |t|
      t.string :provider
      t.string :uid
      t.references :user, foreign_key: true

      t.timestamps

      add_index :authorizations, :user_id
      add_index :authorizations, [:provider, :uid], unique: true
    end
  end

end

Dropping the database should delete all pending migrations. Is there an earlier migration that creates the table and index? — Tom Aranda
– Tom Aranda, Commented Oct 25, 2017 at 15:49
@TomAranda Dropping the database is an absolute last resort and often causes more problems (such as data loss or doing nothing at all if you backup-drop-restore to avoid losing all your data) that it solves. — mu is too short
– mu is too short, Commented Oct 25, 2017 at 17:18
@muistooshort, A fair point. I did not mean to recommend that course of action, and I do not recommend that course of action. However, the user said he already tried that, so I was puzzled why it didn't work. — Tom Aranda
– Tom Aranda, Commented Oct 25, 2017 at 17:22

mu is too short · Accepted Answer · 2017-10-25 17:16:26Z

9

This:

t.references :user, foreign_key: true

adds an index on authorizations.user_id for you. If you check the references documentation it will point you at add_reference and that says:

:index
Add an appropriate index. Defaults to true. [...]

So index: true is the default when you call t.references :user and that creates the same index that add_index :authorizations, :user_id creates.

answered Oct 25, 2017 at 17:16

mu is too short

436k71 gold badges863 silver badges822 bronze badges

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

4 Comments

Jake Over a year ago

Tried to resolve this by removing add_index :authorizations, :user_id add_index :authorizations, [:provider, :uid], unique: true

mu is too short Over a year ago

So you removed add_index :authorizations, :user_id and re-ran your migrations?

Jake Over a year ago

It actually errors with this instead: PG::DuplicateTable: ERROR: relation "authorizations" already exists : CREATE TABLE "authorizations" ("id" bigserial primary key, "provider" character varying, "uid" character varying, "user_id" bigint, "created_at" timestamp NOT NULL, "updated_at" timestamp NOT NULL, CONSTRAINT "fk_rails_4ecef5b8c5"

mu is too short Over a year ago

Did you create the table by hand somewhere? Something else out of the ordinary? Each migration should run inside a transaction and PostgreSQL supports DDL transactions (i.e. it can rollback schema changes) so the whole migration should run or not run. That PG::DuplicateTable exception is telling you that something created the authorizations table before you tried to run the updated migration.

Giggs · Accepted Answer · 2020-09-01 13:13:45Z

0

Check if it's a table or a changed table, most times you need to drop the table or delete the column then run the migration again and it'll be good to go

answered Sep 1, 2020 at 13:13

Giggs

1,08912 silver badges17 bronze badges

Comments

Jake · Accepted Answer · 2017-10-25 17:55:10Z

-1

So the only thing that I've found that "worked" was to actually delete the migration files then run rails db:migrate. Didn't catch on anything, no errors.

Not a fan of the fact that this worked.

answered Oct 25, 2017 at 17:55

Jake

1,3784 gold badges26 silver badges53 bronze badges

2 Comments

oowowaee Over a year ago

If this is the only thing that worked for you, then there is an issue with your migrations. In the above comment you stated that you received a different error - had you rolled back the migration, removed the relevant lines, and then rerun it? The correct solution here is to fix the migrations - deleting the files will do nothing for someone who subsequently comes along and checks them out of version control.

Jake Over a year ago

So I was able to get it to work after messing up in deployment to Heroku. I recreated the merge files without add_index and it works.

user22029208 · Accepted Answer · 2023-08-04 10:22:11Z

-1

The solution for me was to drop the indexed column that had a unique constraint and then reindex

answered Aug 4, 2023 at 10:22

user22029208

1 Comment

Community Over a year ago

As it’s currently written, your answer is unclear. Please edit to add additional details that will help others understand how this addresses the question asked. You can find more information on how to write good answers in the help center.

Collectives™ on Stack Overflow

Rails error: Index name '...' on table '...' already exists while running rails db:migrate

4 Answers 4

4 Comments

Comments

2 Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

Comments

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related