Search code examples
mysqlsqlauto-increment

MySQL autoincrement value increases even when insertion fails due to error


As I receive data from the user, I validate the format of the input every time (PHP). The only thing I can't validate (or I wouldn't want to) is the constraint (for example foreign key) violation. I let the DML to raise and error and the database driver to throw an exception. After catching it I just print out some error message by MySQL's error code.

The problem is, even when insertion fails, I see that the autoincrement sequence still grows, increases. How can I prevent that?


Solution

  • That is how auto increment values and sequences work. When a value is used, it is not reclaimed if the transaction fails, and the transaction is rolled back.

    Since you are using auto increment values, the actual value does not matter so the spaces should not be a problem, apart from the aesthetics of having missing numbers.