Add auto increment back to primary key column in Rails

Question

By mistake I removed the autoincrement option from id field of my table. Can anyone tell me how I can reinsert the option of autoincrement back through migration?

Answer 1

Try:

change_column :my_table, :id, :primary_key

or

my_table.change_column :id, :primary_key

Certain Rails database adapters may not let you call change_column on the primary key. If that is the case then you can always call execute to perform the change using SQL directly:

MySQL:

execute('ALTER TABLE "my_table" CHANGE "id" "id"
  bigint DEFAULT NULL auto_increment PRIMARY KEY')

PostgreSQL (method 1):

max_id = execute(%%Q{SELECT id FROM "my_table" ORDER BY "id" DESC
  LIMIT 1}).to_a.first
execute(%%Q{CREATE SEQUENCE "my_table_id_seq" START #{max_id+1}})
execute(%%Q{ALTER TABLE "my_table" ALTER COLUMN "id"
  TYPE bigint})
execute(%%Q{ALTER TABLE "my_table" ALTER COLUMN "id"
  SET DEFAULT nextval('my_table_id_seq'::regclass)})
execute(%%Q{ALTER TABLE "my_table" ADD PRIMARY KEY("id")}) 

PostgreSQL (method 2):

max_id = execute(%%Q{SELECT "id" FROM "my_table" ORDER BY "id" DESC
  LIMIT 1}).to_a.first
execute(%%Q{ALTER TABLE "my_table" RENAME COLUMN "id" TO "id_orig"})
execute(%%Q{ALTER TABLE "my_table" ADD COLUMN "id" bigserial NOT NULL})
execute(%%Q{UPDATE "my_table" SET "id"="id_orig"})
execute(%%Q{ALTER SEQUENCE "my_table_id_seq" RESTART #{max_id+1}})
execute(%%Q{ALTER TABLE "my_table" DROP COLUMN "id_orig"})

If you do not want to use bigint/bigserial (64-bit), use int(11)/integer/serial instead.