0

I want to add a new primary key auto-incremented column with alter command but I don't want to start it with 1.

Note: My table has data for other columns so i want to add a new-column that starts its auto increment with some number other than 1.

So is there any way to achieve this with the alter command to add column.

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
  • possible duplicate of [How to make a primary key start from 1000?](http://stackoverflow.com/questions/2130635/how-to-make-a-primary-key-start-from-1000) – Vitalii Pro Jul 15 '15 at 09:30
  • No, my table has existing data and i want to add new column with auto increment type – Aman Aggarwal Jul 15 '15 at 09:39
  • Lets say you want the auto increment to start at N, then, can you set the new column's value to N-1 to one of the existing record? That will do the trick – Wand Maker Jul 25 '15 at 17:21
  • No, i want to insert the value from 1st record for the new column, and by this trick we need to do manual entry. that can be done by update command for sure – Aman Aggarwal Jul 25 '15 at 17:23

2 Answers2

-1

I research, do experiments and found that this is not possible to add the new column in existing data table with auto increment that starts with number another then 1.

Aman Aggarwal
  • 17,619
  • 9
  • 53
  • 81
  • I noticed that you can have only one auto_increment column in a table. I had a table with two rows (id = 1 and 2), and I made id not auto_increment, and added new column `test` with auto_increment column - the existing records got values 3 and 4 for `test` column. How do you explain that? – Wand Maker Jul 25 '15 at 17:25
  • you are totally different thing.. My question is how we can add a new column in existing data table and the new column starts with auto increment value 10 (N). – Aman Aggarwal Jul 25 '15 at 17:26
-1

This can be done 

ALTER TABLE my_table AUTO_INCREMENT = 1000

ALTER TABLE my_table ADD my_table_id INT PRIMARY KEY AUTO_INCREMENT FIRST;
vsingh
  • 6,365
  • 3
  • 53
  • 57