Suppose there is a TBL table with an ID field of type INT, primary key, auto increment If we constantly add and delete rows, so that the index reaches the INT_MAX value, then what should happen? Experimentally, I found out that the index in my database overflowed and became negative, and I could not remove the row with a negative index. I'm not sure that this is normal behavior. So I would like to find out how the database should behave when the index overflows. Return an error or make the index negative.

  • one
    I can say that I ran across an index overflow in mysql (32bit). After reaching the maximum, it was impossible to insert more records into the table, although there were no records in the database. - KoVadim

1 answer 1

To begin with, this is not called "index overflow", but "auto-increment field overflow".

The consequences depend on a specific DBMS, simply because auto-increment fields are a feature of concrete implementations, they do not exist in the theory of relational DBMS.

Most DBMSs in this case will generate an error when trying to insert a record into a table, but some may try to do something else, like resetting the counter to zero, to negative numbers, or trying to find a “hole” in existing data.

In the general case, it is precisely known that nothing will happen - no DBMS will insert a record with a repeated primary key into the table.

By the way, the negative value, although strange for an auto-incrementing primary key, is quite a correct value for the INT data type, so it’s not quite clear why you couldn’t delete it. Perhaps it was a mistake in your tool to access the database, and not in the database itself. Try to delete such an entry with a SQL query:

DELETE FROM tbl WHERE id = -2147483648

Avoiding this effect is easy - use BIGINT instead of INT. Such a field will “hold out” until the overflow is 4 billion times longer — that is, for sure longer than your program lasts