Perhaps this is the same poster I just replied to. The auto increment will not go back and fill in blank numbers from deletion. this is for a VERY good reason. One being that the number is still in use tucked away in the hidden system tables of the RDBMS. If it is important that your auto increment numbers are all sequential, especially if it's your primary key, then your data model and/or programming flat out stink. Believe me when i say the people that developed the RDBMS in the first place know what they are doing. This behavior is normal and preferred. Thsi auto number also holds things like user id and timestamps with those id's that are supposed to remain unique for good reasons.

Reply

I dont under stand this at lol

Reply

Then why post?

And for god sakes when you're quoting someone KEEP THE QUOTE TAGS AROUND IT

Reply

I know this thread is a bit older but given my previous response does not seem to have been clear enough I thought that I would clarify. Firstly RDBMS = Relational Database Management System - Such as MySQL Oracle SQL Server and so on. the RDBMS should not be confused with the term database as a database is your personal collection of data whereas the RDBMS is the underlying program that supports the data and allows you to manipulate the data. Thus calling MySQL a database is like calling MS word an office document.

In every database there are system tables as well the normal data tables. The system tables are placed in the database by the RDBMS. These system tables store information such as the names of the databases tables, the names and data types of the fields in tables and so on. These system tables also store what auto increment numbers have been used. These system tables are very important and should not be played with.



This statement seems fairly self explanatory. I can think of no reason whatsoever why an auto increment field would need to be sequential. For the Primary Key of a table there is no reason at all. Primary keys need always to be unique. A primary key should never ever be reused. Just for example what happens if you need to restore a table where you have reused the primary key but, the backup has the original owner of that PK. Your data in that table and the ones related to it are not going to congruent.

Lastly all because you may not have a record in your database any more does not mean that the RDBMS does not know it was there. In the database logs there will be activity records for the primary keys of what has been done to record when and by whom.

Perhaps this more lengthy explanation clarifies my previous post or maybe it has obfuscated it.

 

 

 

Reply