When inserting a new record into a table, for certain reasons it may not be inserted (for example, the column has a unique constraint and this query violates it), but nevertheless the id column in which the AUTO_INCREMENT property is set is incremented. Is it possible to somehow change this behavior?

  • 3
    It is possible, but not necessary. Or do you, when you delete the record, also want to correct the indexes? - KoVadim
  • As far as I know, this is a feature of the engine. But is it so critical? - etki
  • 3
    no need to worry about indexes. This is a personal matter engine. If these indices affect the display of data - then you are doing something wrong. - KoVadim
  • one
    @ Jeremen1, can it really harm at least something other than perfectionism? - etki
  • one
    @Fike, there is still an overflow, especially if the "wrong" inserts are several hundred / thousand per second (this is probably an architecture error, but nevertheless, it happens that they rely on keys and only on them when they are inserted) - zb '

1 answer 1

You should not use the values ​​generated by the AUTO_INCREMENT mechanism to create unbreakable sequences. Its main task is to create unique values. Gaps in the sequences of generated numbers is a regular situation. Moreover, it is necessary for the integrity of the database.

For example, if you have a table of articles with AUTO_INCREMENT identifiers. This identifier is often used in the link to the article. If you delete an article, the sequence will be broken. It is impossible to fill this sequence in any way, even if you have removed all links to it within the database, to the old article there can be links from the Internet. Therefore, old articles will lead to a page with a new text, which will lead to a violation of data integrity.

This example is not entirely in the context of your question; however, by creating mechanisms for circumventing gaps in AUTO_INCREMENT, you are moving in the direction of violating data integrity. In a large system, tracking errors will be very difficult. For the reliability of the future system, it is better to accept AUTO_INCREMENT as it is: a break means a break. If the system grows to large volumes it will be easier and cheaper to maintain it.

If you need indissoluble numbering of articles, it is better to provide for this a separate field, not tied to the AUTO_INCREMENT mechanism - it has other tasks.