rails migration remove keys and add data into auto-increment column id

Question

I have php application with below table structure

CREATE TABLE IF NOT EXISTS `artist_has_fans` (
`artist_id` int(11) NOT NULL,
`fan_id` int(11) NOT NULL,
 PRIMARY KEY (`artist_id`,`fan_id`),
 KEY `fk_artist_has_artist_artist2` (`fan_id`),
 KEY `fk_artist_has_artist_artist1` (`artist_id`)
 ) ENGINE=MyISAM DEFAULT CHARSET=latin1;

I need to write migration in rails so that table has primary key id with autoincrement. Secondly since the table already has some data I need to insert data for id column. Then I need to remove Primary key from existing columns.

Any help on writing migration is appreciated.

Doon · Accepted Answer · 2013-04-26T12:48:23.640

In looking at the above it looks just like a join table. Do you plan on adding other attributes to it or are you just trying to make it work with Rails? you can use just use a has_and_belongs_to_many relationship inside rails and you will not need an ID field.

Something like below should work without having to modify your table.

class Artist < ActiveRecord::Base
   ...
   has_and_belongs_to_many :fans, :join_table => 'artist_has_fans'

end

but if you want to alter the table to add the primary key in mysql

   def_up
      execute <<-SQL
       ALTER TABLE artist_has_fans ADD id INT PRIMARY KEY AUTO_INCREMENT;
      SQL
   end

That should create the id and add values for all the existing rows

rails migration remove keys and add data into auto-increment column id

1 Answers1