Auto_increment after a delete

#1

Today i am working on my member pages. I want to add a username and password changing form. Suddenly, i realized that if a member is to be deleted, then what happens to the database row according to auto_increment? is the table automatically updated? I searched a bit and i see that the row is removed but nothing is changed in the auto_increment values. I see that one can use ALTER TABLE tablename AUTO_INCREMENT = x; to reuse that id but many people suggest to not reuse the id. I assumed that reset means that mysql will recount/move rows to fill in missing numbers. But this is not the case. So is okay to have missing numbers? it bothers me because it is unorganized but i can let it go if it is normal.

However, i also wonder how large should i make an id number to accomodate active members and deleted members? i currently have id int(11). I understand this to mean 11 digits (10,000,000,000) thus 10billion possibilities. Is this correct? I’m really nescient about this subject. I really do not know the answer.

Thank you.

#2

This is normal, expected and wanted. Simple example. A website has job listings, and I bookmark listing 1164. The next day this listing is deleted, and a new one is registered. My bookmark now goes to the new listing and I (the user) is thoroughly confused.

So to maximise your ID columns (where there will always be positive numbers, just use unsigned.

1 Like
#3

wow! i need to spend more time reading the manual and less time searching Google. I, like other fools, had assumed that int(11) sets the maximum size for the column. Thank you for correcting me. Now i see how foolish it is to specify int(11) along with unsigned and not a zerofill to be seen. I have no idea why i thought that sql would be any different than c++. integer is integer with a predetermined size. Which, now i realize that i should be implementing a smallint with my db. I doubt that i will have 65000 paying members.

Thank you, Jim.

#4

Also, you don’t do hard deletes. You have a column that flags the record as deleted and filter on it when doing a query.

#5

Hello, astonecipher. Thanks for the tip. Although, i think that i need to research the subject. I’m not sure if maintaining user records after a subscription has ended is a violation of EU privacy laws.

#6

Well it would be quite annoying re-registering to every site you subscribe to whenever the subscription runs out

#7

Hi Jim, i agree but i don’t make the laws. I’m not sure if the EU laws prevent soft delete database actions or not. I need to look into it. Better to be safe than sorry. However, i would prefer using a transaction to move deleted users to a deleted users table instead. Plus delete the password field after moving the data.