0

If I have 100 records in a table with primary keys that are random e.g. 1, 552 etc. I want to keep these IDs but I want to start adding new records. Is the best idea just to start the auto increment index at the highest primary key value + 1 in the table?

Dharman
  • 30,962
  • 25
  • 85
  • 135
Aiden Rigby
  • 569
  • 1
  • 5
  • 7
  • Yes, that is a good approach. – Gordon Linoff Oct 05 '15 at 02:32
  • Okay cool, hypothetically if I added a record and my auto increment index was the same as an existing primary key index would it still use that index? – Aiden Rigby Oct 05 '15 at 02:35
  • . . . I don't fully understand your comment, but auto incrementing and primary keys are two different things (that are often properties of the same column). – Gordon Linoff Oct 05 '15 at 02:40
  • Look at this answer http://stackoverflow.com/a/21461100/382936. You can skip the `DROP` query if your table doesn't already have a primary key. MySQL will automatically compute the auto_increment value from the column's existing values if you don't provide a value. – Seth Difley Oct 05 '15 at 03:59

1 Answers1

1

You would need to get the max value of the column and alter table to set the next auto_increment value:

SELECT MAX(ID) + 1 FROM table_name INTO @maxauto;
ALTER TABLE table_name AUTO_INCREMENT = @maxauto;

Since the table has an auto_increment field and the value is being generated from it, handling of the id value through a function will be tough to manage. In other case, you can manage to get the unused ids from the table and use it as the id.

James Jithin
  • 10,183
  • 5
  • 36
  • 51
  • If you use this approach, be sure to run both queries inside the same transaction. Otherwise, you risk having a change made to the table by another user or process between the execution of the first (`SELECT`) and second (`ALTER`) queries. – Seth Difley Oct 05 '15 at 04:02