CODEWITHSUNDEEP
ruby-on-rails
Josh Glover
Josh Glover

Reputation: 26140

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

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?

Upvotes: 6

Views: 4249

Answers (3)

Dylan Markow
Dylan Markow

Reputation: 124419

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})

Upvotes: 10

Jan Minárik
Jan Minárik

Reputation: 3267

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).

Upvotes: 6

Anders Arpteg
Anders Arpteg

Reputation: 337

Unfortunately, rails do not support the :limit option in add_index (at least not in 2.x). However, you can always use execute instead and enter the sql directly.

Upvotes: 0

Related Questions