Auto_Increment problems 2

[Guest_imported]

A little scenario that I’m having some headaches over…

I have an ‘ID’ column with auto_increment set, and I create 6 rows, like thus

ID
1
2
3
4
5
6

Now, if I were to ‘delete from table where id = 2’, it would become

ID
1

3
4
5
6

mySQL doesn’t seem to delete the row, but rather just the content within. I say this because if I were to now ‘insert into table values (‘’)‘, I would get this

ID
1
7
3
4
5
6

Is there any way to get around this? It is most annoying. I know the work-around solution by using “order by”, but I’d rather avoid that if at all possible.

 

[rycamor]

ORDER BY is not a "work-around"! This approach comes from a misunderstanding about what an auto_increment column is supposed to do. It's purpose is NOT to keep a running count of the number and order of records in your table. Rather it is a method to automatically insure uniqueness of your primary key. This is for data integrity purposes. If the system re-ordered your primary key every time you delete a row, any related rows in other tables become useless. So, if you really want to build serious database-backed applications, I recommend you never get in the habit of relying on the primary key for row and ordering information.

However, you can easily "fill in" any gaps in your primary key simply by inserting a record specifying that missing id number manually. Afterwards, any further INSERTS will automatically pop back to the latest auto_increment number.

In other words: you delete record with id 10 from your table. Now, your next insert could say INSERT INTO mytable VALUES(10,'blah','blah'). The auto_increment doesn't try to do anything if you explicitly set that number. Then, for your next record, you go back to standard mode: INSERT INTO mytable VALUES('','blah','blah'), and the auto_increment will start where it left off.

Unfortunately, MySQL doesn't have Views, which is where you would normally have your row count information, since that information is not integral to your data, but rather just an incidental. Also, in any programming language, you can easily increment a row count as you are looping through your database results and outputting data.

Simply put: row counts and ordering change, primary keys do not (should not). -------------------------------------------

"Calculus is just the meaningless manipulation of higher symbols"
                          -unknown F student

 

[steve1]

First, rycamor was 100% correct with the explanation. I would add just a few things.

SOME people feel that deleting an autoincremented key should cause it to reuse that number. Both sides have their own reasons. However I would give you THIS example:

Suppose that ID is used as a customer number, and the data is snowflaked. Deleting a customer and adding a new one(with the same key) COULD cause a LOT of problems.

SOME will talk about using foreign keys.(To prevent deletes of a main record when related dimensional data exists) These are not an option with MYSQL, and may not be fully implemented in the schema anyway!

SOME will talk about cascading deletes.(To remove dimensional data when a main record is deleted.) These are ALSO not an option with MYSQL, and ALSO may not be fully implemented in the schema anyway! Besides, this is just plain scary.

I would say LIVE with the constantly increasing keys, etc... unless it is a static table that you can easily change, or you have run out of numbers. (This is unlikely with MYSQL).

Steve

BTW. Some databases, and as I recall MYSQL is one, MAY eventually reuse keys if they can't continue to increment. Also, some allow you to insert using a key that was deleted earlier. For these reasons, the autoincrementing key is really only good for a cross application primary key, and isn't even a reliable way to determine the sequence of inserts.