Create missing auto increment attribute with rails migration

Question

I'm writing a migration to convert a non-rails app into the right format for rails - one of the tables for some reason does not have auto increment set on the id column. Is there a quick way to turn it on while in a migration, maybe with #change_column or something?

Answer 1

You need to execute an SQL statement.

statement = "ALTER TABLE `users` CHANGE `id` `id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT"
ActiveRecord::Base.connection.execute(statement)

Note this is just an example. The final SQL statement syntax depends on the database.

Answer 2

If you're on postgesql, a single request won't make it. You'll need to create a new sequence in the database.

create sequence users_id_seq;

Then add the id column to your table

alter table users
    add id INT UNIQUE;

Then set the default value for the id

alter table users
    alter column id
         set default nextval('users_id_seq');

Then populate the id column. This may be quite long if the table has many rows

update users
    set id = nextval('users_id_seq');

Hope this helps postgresql users...