# Need to give unique names to indexes in sqlite3

**URL:** <https://www.ruby-forum.com/t/need-to-give-unique-names-to-indexes-in-sqlite3/120518>\
**Category:** Rails\
**Created:** [November 17, 2007, 9:58pm UTC](https://www.ruby-forum.com/t/need-to-give-unique-names-to-indexes-in-sqlite3/120518 "2007-11-17T21:58:10Z")\
**Posts on this page:** 1\
**Page:** 1

<div class="post-metadata">

**Author:** ![jfrankov](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/87869e/32.png) [@jfrankov](https://www.ruby-forum.com/u/jfrankov)\
**Post date:** [November 17, 2007, 9:58pm UTC](https://www.ruby-forum.com/t/need-to-give-unique-names-to-indexes-in-sqlite3/120518/1 "2007-11-17T21:58:10Z")

</div>

I’m trying to use sqlite3 as my testing engine for cruisecontrol (I’m  
using Geoffrey Grossenbach’s memory-test-fix plugin for this). When CC  
tries to build the schema in sqlite3, this occurs:

[snip]  
– add\_index(“events”, [“bookmark\_id”], {:name=\>“bookmark\_id”})  
-\> 0.0005s  
[other stuff snipped]

– add\_index(“flags”, [“bookmark\_id”], {:name=\>“bookmark\_id”})  
/usr/bin/ruby -Ilib:test “/usr/lib/ruby/gems/1.8/gems/rake-0.7.3/lib/  
rake/rake\_test\_loader.rb” SQLite3::SQLException: index bookmark\_id  
already exists: CREATE INDEX “bookmark\_id” ON flags (“bookmark\_id”)

Based on this error, it seems to me that the namespace for indexes in  
sqlite3 is database-wide, so when i make a bookmark\_id index for  
events, i can’t make the same index named “bookmark\_id” on the flags  
table.

So I thought I’d give an explicit name to each index, hoping that the  
sqlite3-ruby gem would honor it. I changed my migrations to say this:

```
execute "alter table events add index bookmark_id_idx_events

```

(bookmark\_id)"  
execute “alter table flags add index bookmark\_id\_idx\_flags  
(bookmark\_id)”

…and it looks like the schema.rb picks up the change:

add\_index “events”, [“bookmark\_id”], :name =\>  
“bookmark\_id\_idx\_events”  
add\_index “flags”, [“bookmark\_id”], :name =\> “bookmark\_id\_idx\_flags”

…but when sqlite3 runs the schema.rb to build the db, it is ignoring  
the :name attribute, tries to make a second bookmark\_id index, and  
keeps giving the same error.

My questions are:

1. does the sqlite3-ruby gem support named indexes?
2. If so, what am I doing wrong?

All advice is greatly appreciated!

Thanks,  
-Jason