Thursday, March 26, 2009

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.

1 comment:

  1. Hmmm nice pointer! Tho I never used sqlite3 over mysql. Who knows tomorrow i mite need to :-)