AI ID remembers

[freelance84]

The ID field in my 'members' table seems to remember passed numbers even when a member has been deleted:

 

i.e:

There are ten members in my table, all have been added one after the other and their ID's are 1-10 respectively. Then member ID 7 is removed. Why or rather how can I get a new member to take the number 7 slot?

 

At the moment each new member is given the next consecutive number in the ID list and the table even remembers numbers that have been deleted

i.e:

If the member with ID number 10 got deleted the next ID given would be 11 even though there is no number 10.

 

Does anyone know what this is called or how to work around this?

[Mchl]

This is normal behaviour and should not be changed.

[freelance84]

Does this behaviour have a specific name?

 

I'd like to understand a bit more about it but not sure what its called and it not covered in my tutorial books

[Mchl]

That's how auto incremented primary key is supposed to behave. It's done like that intentionally.

 

Suppose you have your 10 users, and user with ID = 7 is deleted. If you wanted to reuse this ID for a new user, you would have to delete records in other tables refering to delted user. Otherwise, they'd be 'attached' to a new user which in majority of cases is undesirable.

 

That's why you should not reuse primary key values.

[freelance84]

I thought that might be the logic behind it, but was prepared to set any "delete members" buttons to also remove any info of theirs from other tables.

 

Does this built in function also hide/ keep the rest of the members info somewhere or does it simply remember what ID numbers have been used in the past and not use them again?

[Mchl]

Whether or not related data from other tables should be deleted depends on application needs.

 

It just remembers what number was used last, and always tries to issue a larger number (usually larger by one). Once maximum number that can be stored in this column is reached, it will try adding this maximum number for each new record, which in case of a primary key will throw an error.

 

Try running

SHOW CREATE TABLE yourTableName;

query, and you will see, that there indeed is a last used auto increment number attached to table definition.

 

 

[freelance84]

Ah, genius. Thanks for your help