There is a string id = 1 and some field with the index UNIQUE.
I try to insert another line and get an error about duplicating the value in the index.
I try again to insert a string, but with a different value of the unique field and the string is inserted, but id will be equal to 3.
How to roll back or ignore autoincrement with duplicate? Why did they do that at all?

  • one
    No It is necessary to build a database in such a way that the numbering breaks in the ID are irrelevant. The DBMS in general has the full right to issue IDs not even in ascending order (although it usually does not do this). For what they did - for quick work inserts. To be able to roll back id, you need to do a full table lock at the time of insertion, and some transactions with inserts can go for half an hour and no one at this time can make other inserts. Notice - almost all DBMSs that allow automatic assignment of IDs behave just like this - Mike
  • Understood thanks! Then, instead of unique, you can use a simple index, and before inserting, check for the existence of such a value. Only an extra request is obtained - Anton
  • And where does the uniqueness? Uniqueness and autoincrement are different concepts. A field can be unique without auto-increment and if the logic of the work implies that the same values ​​cannot be unique. You better tell me what the ID = 2 passes to you, in 90% of cases, if you need sequential numbering, you have approached the task from the wrong side. And by the way, be careful with "first check, then insert", between these two operations, another process can produce its insert. If this is done, then in one request, the benefit of SQL allows it. - Mike
  • Well, for example, registration. The email field is unique. The person sends the request, everything is OK, only the email is duplicated - we return the message, and so some fool will send many times, and the sequence id is broken. And I want accuracy. - Anton
  • The accuracy is that IDs are unique during the entire existence of the table. But it’s not at all that they are like "without holes". - Akina

1 answer 1

In mysql databases, the id key in the table is unique and should not be repeated. If you want to find several elements by the value of one field, it makes sense to enter your own field, for example my_id .

  • You can change the id, and the counter can be changed, from which the addition will continue - Anton
  • Thanks, corrected - Viktor