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?

score 21 · Answer 1 · edited Sep 27 '11 at 13:51

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.

Thnx for yor response,But i have actually already done change_column on some of the primary keys.May be thats why it removed the already existing Auto Increment option from the primary key option. — , Mar 05 '09 at 15:41

score 8 · Answer 2 · answered Jan 03 '17 at 16:43

8

I didn't check other versions but on Rails 5 you can just set the auto_increment option:

change_column :table_name, :id, :int, null: false, unique: true, auto_increment: true

Or if you want a bigint:

change_column :table_name, :id, :bigint, null: false, unique: true, auto_increment: true

answered Jan 03 '17 at 16:43

drewish

9,042
9
38
51

need more option, that is `primary_key: true` – Mada Aryakusumah Apr 03 '20 at 05:04

score 1 · Answer 3 · answered Jul 30 '09 at 11:09

1

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

answered Jul 30 '09 at 11:09

Leonid Shevtsov

14,024
9
51
82

score 1 · Answer 4 · answered Dec 17 '20 at 10:19

1

This worked for me, created a primary column and gave ids to all the rows.

add_column :tags_posts_rel, :id, :primary_key

answered Dec 17 '20 at 10:19

Ankur Kothari

822
9
11

score 0 · Answer 5 · answered Jun 23 '20 at 14:34

For those of you who came here (like I did) in an effort to figure out how to make a custom id column that uses bigint instead of int, what I didn't realize is that for Rails 5.1 and above, bigint is the default type for id

https://github.com/rails/rails/pull/26266

score 0 · Answer 6 · answered Oct 23 '20 at 05:40

Change Column

Rails5

change_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Rails6

change_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Add Column Rails5

add_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Rails6

add_column :table_name, :id, :int, null: false, unique: true, auto_increment: true,  primary_key: true

Add auto increment back to primary key column in Rails

6 Answers6