CODEWITHSUNDEEP
mysqlruby-on-railsdatabasesqliteschema.rb
Sriharsha
Sriharsha

Reputation: 111

Is it possible to have same index name within a sqlite database?

I have a Ruby on Rails app and development database is in Mysql. I decided to do use sqlite database for testing. To accomplish that, I did the following:

  1. Configured database.yml file to support test.sqlite3 for testing environment

  2. Took a dump of current development database schema using rake db:schema:dump

  3. Tried using the schema.rb to generate tables in a test.sqlite3 database file by using the command. rake db:test:clone

Then it went boom. I got an exception 

SQLite3::SQLException index XXX
already exists:CREATE INDEX "XXX" ON table ("XXX")

When I carefully surveyed the migration files and schema, I realized that same index name is used in more than one table. For example, some statements in the schema.rb are like this:

add_index("patients", ["appointment_id"], {:name=>"appointment_id"})
add_index("doctors", ["appointment_id"], {:name=>"appointment_id"})

I know that mysql maintains uniqueness of index name within the scope of a table. Now a simple solution may be to revert to mysql for test database. What I am not able to figure out is whether it is possible at all to generate an sqlite file from schema.rb in this particular case.

Now comes the weird part. I made the following changes and tested in a dummy app.

add_index("patients", ["appointment_id"], {:name=>"pappointment_id"})
add_index("doctors", ["appointment_id"], {:name=>"dappointment_id"})

It worked! Now I may be tempted to do the same in my real app. The harsh reality is there are many places where I will need to do the renaming and in production, it would be more than a tedious task as we have million of records which get affected if I drop old indexes and add new ones (Mysql 5.5). Is there a way out?

Upvotes: 2

Views: 2054

Answers (1)

ruby_newbie
ruby_newbie

Reputation: 3285

You are trying to add an index that already exists in your test database. So the index of appointment_id already exists on the patients table of your test db. You can try RAILS_ENV=test rake db:drop db:create db:test:clone and it should run fine. That said I like to add conditionals to my migrations to avoid problems. for example: 

add_column :patients, :appointment_id, :integer unless column_exists? :patients, :appointment_id

Please for the love of all that is holy do not rename your prod columns to pappointment_id or anything of that sort.

Upvotes: 0

Related Questions