title: Is it possible to have same index name within a sqlite database?
body: <p>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:</p>
  
  <ol>
  <li><p>Configured <em>database.yml</em> file to support test.sqlite3 for testing environment</p></li>
  <li><p>Took a dump of current development database schema using <code>rake db:schema:dump</code></p></li>
  <li>Tried using the schema.rb to generate tables in a test.sqlite3 database file by using the command. <code>rake db:test:clone</code></li>
  </ol>
  
  <p>Then it went boom. I got an exception </p>
  
  <pre><code>SQLite3::SQLException index XXX
  already exists:CREATE INDEX "XXX" ON table ("XXX")
  </code></pre>
  
  <p>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:</p>
  
  <pre><code>add_index("patients", ["appointment_id"], {:name=&gt;"appointment_id"})
  add_index("doctors", ["appointment_id"], {:name=&gt;"appointment_id"})
  </code></pre>
  
  <p>I know that mysql maintains uniqueness of index name within the scope of a table. Now a simple solution may be to revert to <strong>mysql</strong> 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.</p>
  
  <p>Now comes the weird part. I made the following changes and tested in a dummy app.</p>
  
  <pre><code>add_index("patients", ["appointment_id"], {:name=&gt;"pappointment_id"})
  add_index("doctors", ["appointment_id"], {:name=&gt;"dappointment_id"})
  </code></pre>
  
  <p>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?</p>