-1

I've tried to use ALTER TABLE tablename AUTO_INCREMENT = 1; It's not resetting the increment to 1. I don't know why. I've had to TRUNCATE table and reenter the values so the primary key would auto_increment starting at 1. I've seen this guide (http://befused.com/mysql/reset-auto-increment) and for what I understand of it, you can only reset the autoincrement with a number that's equal to the Max number in that table +1. Meaning, if a have a number 12, I can only reset the increment to start with 13. Is that correct? Or is there something that can stop my alter table command from actually resetting the increment to 1?

Learning Java
  • 41
  • 1
  • 5
  • I did try to use it, but it's just not resetting. I was wondering if anyone could enlighten as to WHY it could be not allowing me to reset. I pretty newbie so I don't know if there's something that could prevent the resetting. – Learning Java Jan 02 '18 at 11:46
  • As the other question and answers explain, you can't reset the "auto increment" value to a value lower than the current highest Id. If you do you would run in problems reusing the same Id twice. Keep in mind that "auto increment" is not for counting your rows. – Progman Jan 02 '18 at 11:48
  • Then, that's what I meant in my question, and the answer is "One cannot reset it to a number lower than the MAX already present in that table"? Then, the fact that the syntax "ALTER TABLE tablename AUTO_INCREMENT = 1" is not working is solely because of that rule? – Learning Java Jan 02 '18 at 11:52
  • As state on https://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql/8923132#8923132, yes. – Progman Jan 02 '18 at 11:58

1 Answers1

0

Is it really important that your index starts at one, I wouldn't think it would be as massive issue and it is probably safer not to assume that the index is beginning with one

Oliver Gee
  • 26
  • 1
  • I did see a few people mentioning this in other places. I do understand it, however, it just looks weird to me when I see a few numbers skipped on the column (when I get an error and cant insert data on a registry, for example). But I'm truly asking because I want to understand whats not allowing me to do that. I did check my table with select * from table, and the primary key column had increments starting at 10, example, and it just remained 10 after I typed the code to reset it. – Learning Java Jan 02 '18 at 11:44
  • I remember having the same issue myself once, what error do you get when you try to insert into the DB – Oliver Gee Jan 02 '18 at 11:47
  • Surprisingly, none! it just says query OK. It does mention that no rows were affected. And it doesn't reset... – Learning Java Jan 02 '18 at 11:48