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?

Check this link : https://github.com/rails/rails/issues/9855#issuecomment-28874587 Might sure be helpful, works very well on my side — Ben, Aug 06 '14 at 08:40

Dylan Markow · Answer 1 · 2011-05-05T14:33:43.300

10

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

edited May 05 '11 at 14:33

answered May 05 '11 at 14:20

Dylan Markow

123,080
26
284
201

The length of the index's **name** is not the problem. – Josh Glover May 05 '11 at 14:30
This doesn't work for me with Rails 2.3.11; what version did you test it on? – Josh Glover May 05 '11 at 15:27
I didn't test it (I don't have MySQL installed), but the Rails 2.3.8 docs show this as an example: `add_index(:accounts, [:name, :surname], :name => 'by_name_surname', :length => {:name => 10, :surname => 15})` – Dylan Markow May 05 '11 at 15:31
That's really odd--do you have a link to that documentation? – Josh Glover May 05 '11 at 15:44
http://rubydoc.info/docs/rails/2.3.8/ActiveRecord/ConnectionAdapters/SchemaStatements#add_index-instance_method – Dylan Markow May 05 '11 at 15:47

score 6 · Answer 2 · answered May 05 '11 at 14:28

6

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:

The parameter gets used for all three keys so you can't specify different lengths for individual keys.
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).

answered May 05 '11 at 14:28

Jan Minárik

3,227
1
17
23

Very nice! I think I can't (or shouldn't) switch adapters here, so I guess I'll go with Anders's `execute` solution, but I'm sure your solution will be appropriate for others in my boat. :) – Josh Glover May 05 '11 at 14:32

score 0 · Accepted Answer · answered May 05 '11 at 14:24

0

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.

answered May 05 '11 at 14:24

Anders Arpteg

337
1
3

Rails 2.x **does** support the :length option. – Dylan Markow May 05 '11 at 14:35
Same link as my other comment -- my comment here meant that while it may not support the :limit option (which I don't see how that affects your problem, since the :limit option has to do with creating/altering fields/columns, not indexes), it does support the :length option. http://rubydoc.info/docs/rails/2.3.8/ActiveRecord/ConnectionAdapters/SchemaStatements#add_index-instance_method – Dylan Markow May 06 '11 at 13:09

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

3 Answers3