CODEWITHSUNDEEP
phpmysql
Sandeep Vishwakarma
Sandeep Vishwakarma

Reputation: 576

Restrict autoincrement on MYSQL when insert duplicate record

When I go to insert duplicate value and ignore that value to insert in column 'tagName' then id is increasing automaitcally but I do not need to jump the ID's Here I have is with a table with two columns -- INNODB - ID(autoincrement), tagName(unique varchar)

when doing INSERT IGNORE INTO tablename SET tagName="something" the autoincrement Id value increases even if the insert was ignored.

for example I'm displaying 3 rows but bad IDs.

ID   tagName
1    test
8    test111
29   test2222

I do not want to use the sub queries

Thanks

Upvotes: 0

Views: 431

Answers (1)

GordonM
GordonM

Reputation: 31730

If I'm reading your question right, then what you're asking is if you can stop autoincrement incrementing if you tried to insert a row and the insertion failed.

The behaviour you're observing is a deliberate design choice that almost all major databases (MySQL, Oracle, PostgreSQL, SQLite, etc) also do, for the reason that autoincrementing columns are almost always used to give a new row a unique ID, and if you attempt to insert a row then the next available value is assigned to it from the autoincrement which then increments itself.

If the insertion failed but another one that occurs at almost the same time succeeds then that new row's ID is already fixed. It's considered very bad practice to change a row's ID once it's been established, especially in a database that makes use of foreign keys, therefore simply allowing a gap in the numbering to exist is a better approach than to attempt to update all the rows that may have been inserted in the time since the insert failure occurred, especially as yet more new rows may be in the process of being inserted while you're updating all those rows. There's going to be a window where the database is in an inconsistent state.

In short, there's no good reason for rolling back an autoincrement value after a failed insert (other than OCD) and plenty of good reasons for not rolling it back.

Upvotes: 1

Related Questions