How do I limit field lengths when creating an index in a Rails migration?

Question

I have a migration that adds an index to an existing table using add_index:

add_index :foo, [:foo_type, :bar_id, :baz_id], :unique => true

However, my fields are long enough that they exceed the maximum InnoDB key length. As per Anders Arpteg's answer to How do I increase key length in MySQL 5.1?, I need to generate SQL like this:

CREATE UNIQUE INDEX `index_matches_on_foo_and_bar_id_and_baz_id` ON `matches` (`foo`(100), `bar_id`(100), `baz_id`(100))

Can I do this in a nice Rails-y way, or must I stoop to using raw SQL in my migration?

Answer 1

You can specify manual lengths for each field you use, to stay under the total limit:

add_index(:foo, [:foo_type, :bar_id, :baz_id], :unique => true, :length => {:foo_type => 100, :bar_id => 20, :baz_id => 20})

Answer 2

You can specify length parameter, this will generate the exact SQL you asked for:

add_index :foo, [:foo_type, :bar_id, :baz_id], :unique => true, :length => 100

Two drawbacks:

1. The parameter gets used for all three keys so you can't specify different lengths for individual keys.
2. This functionality is added in ActiveRecord::ConnectionAdapters::MysqlAdapter, so you have to use MysqlAdapter for it to work (maybe other adapters implement it also, I just know Rails does not support it by itself).