Menu
I have a table of web pages, with the primary key as hash of the URL, and an auto_increment ID column, which is also a unique key.
What I'm a bit confused by is why successive inserts don't increment the ID field by 1. When I first created the table and did a single insert, the first id was 1. The second insert produced and id of 5 and the third was 8.
I have a trigger on the table which, on insert, computes the hash of the URL of the webpage. Not sure if that's relevant or not.
It's not a problem to have gaps, but I would like to understand why successive inserts don't generate IDs with a step size of 1.
thanks!
952
Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table. Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
When the AUTO_INCREMENT column reaches the upper limit of data type then the subsequent effort to generate the sequence number fails. That is why it is advised to use a large enough integer data type for the AUTO_INCREMENT column to hold the maximum sequence value required by us.
The automatically generated value can never be lower than 0. Each table can have only one AUTO_INCREMENT column.
It could have multiple causes: Check if the auto_increment value on the table itself, has the next highest value. Mind that if you have transactions where you INSERT a row and rollback the transaction, that auto_increment value will be gone/skipped.
The default value is Yes. If you want to manually assign a value to a field that has the AutoIncrement property set to Yes, you must be member of the SQL Server db_owner database permission set.
No. A primary key must be unique and that has to be 100% guaranteed, and NON NULL A primary key should be stable if ever possible and not change.
Several suggestions of why this may be happening:
See auto_increment_increment. This controls the incrementation each time a new value is requested during INSERT.
Also if you use InnoDB tables in MySQL 5.1, they optimized auto-inc allocation so that it locks the table for a shorter duration. This is good for concurrency, but it can also "lose" auto-inc values if the INSERT of a row conflicts with another constraint such as a secondary UNIQUE column or a foreign key. In those cases, the auto-inc value allocated is not pushed back into the queue, because we assume another concurrent thread may have already allocated the next auto-inc value.
Of course rollbacks also occur, in which case an auto-inc value may be allocated but discarded.
51
It could be related to transactions that end up getting rolled back. For example,
Then stackoverflow.com is inserted with id=7 and 5 and 6 are left blank.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
