Auto Increment In rails

Question

Hi ,By mistake I have removed the autoincrement option from id field of my table.Can any one tell me ,how can i reinsert the option of autoincrement in table 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.

Cheers, V.

Answer 2

Hi I have implemented yor solution but i want the data type of the id field as Bigint, So it is not allowing me to do both operations ,which are making id a primary key and assigning it a datatype.

Answer 3

Your Postgres code does not work, it's impossible to use serial or bigserial in an ALTER TABLE statement. Correct SQL for PostgreSQL is

ALTER TABLE table ALTER COLUMN id TYPE int 
ALTER TABLE table ALTER COLUMN id TYPE bigint