How do I solve "index unique_schema_migrations already exists" in Rails?

Question

Running rake db:migrate followed by rake test:units yields the following:

rake test:functionals
(in /projects/my_project)
rake aborted!
SQLite3::SQLException: index unique_schema_migrations already exists: CREATE UNIQUE INDEX "unique_schema_migrations" ON "ts_schema_migrations" ("version")

The relevant part of db/schema.rb is as follows:

create_table "ts_schema_migrations", :id => false, :force => true do |t|
  t.string "version", :null => false
end

add_index "ts_schema_migrations", ["version"], :name => "unique_schema_migrations", :unique => true

I'm not manually changing this index anywhere, and I'm using Rails' default SQLite3 adapter with a brand new database. (That is, running rm db/*sqlite3 before rake db:migrate doesn't help.)

Is the test:units task perhaps trying to re-load the schema? If so, why? Shouldn't it recognize the schema is already up to date?

Answer 1

In SQLite, index name uniqueness is enforced at the database level. In MySQL, uniqueness is enforced only at the table level. That's why your migrations work in the latter and not the former: you have two indexes with the same name on different tables.

Rename the index, or find and rename the other unique_schema_migrations index, and your migrations should work.