Multi-Column Database Indices in Sqlite3 and Ruby on Rails

In my previous post about database indices in Sqlite3 and Ruby on Rails, I'd observed that Sqlite didn't seem to support multi-column indices properly. It turns out that while Sqlite does support them, I was using syntax in my Rails model which had the effect of ensuring that both the columns in my index were checked separately for uniqueness. Assuming you have the category_id and name fields in a table such that the combination of those two fields has to be unique, the correct way to do multi-column indices is as follows:
In your database migration,

add_index :contests, [:category_id, :name], :unique => true

In your data model,

validates_uniqueness_of :name, :scope => :category_id

That last line in the model is key. Syntax similar to the add_index line above won't work when using the validates_uniqueness_of validator.

A Scientific View on Database Indices

I've been playing with Ruby on Rails by seeing how easy (or difficult) it would be for me to re-write an existing mod_perl2 application within RoR. While the original application uses PostgreSQL as its backend database (numerous reasons but the top few are: timestamptz, multiple columns in a table that can default to now(), support for transactions - even nested ones), I decided to use sqlite3 for the initial prototype of the application before it grew beyond 2-3 models.

It seems that either sqlite3 or RoR 2.0.5 doesn't support multi-column indices on a table. One of those two converts a single two-column index into 2 one-column indices - not quite the behavior I wanted. I'll try to switch to PostgreSQL to see if the behavior re-surfaces.

While I was scouring Google for sqlite3's limitations, however, I came across this interesting article that details the kind of scientific analysis that should be conducted on your data to determine the order of columns in a multi-column index. Very informative.