Autoincrement Filed

alirezaok

i have field on table. it is (interger and primary and autoincrement).
for example it has 1 to 20 digits as 20 records.

when i delete 15 to 20 records from end of it then insert new record to it
with: INSERT INTO dynamic VALUES('0')

, it give "21" digit inestaed of "16".

why?

how do i change it that when i delete records of end of it, it be give the continue of last digit that are in it?

i think i must get the digit of last record then i incrase it then insert:
INSERT INTO dynamic VALUES('lastrecord+1')
is this way right?

is there any better or simpler way?

Thanks

Drakla

a) That's the way it's supposed to behave

b) what happen if you delete record 10 of a 20 record table, what do you want auto increment to be?

c) alter table yourtable auto_increment = 1;

But auto increment is like this for a reason. You have a member's table and somebody gets ID 100, when they delete their account do you want to give somebody else the ID 100 later on? No - they might end up with access to fields that haven't been deleted from other tables that you might have forgotten to remove, as the most basic example.

alirezaok

yes thanks.

the same problem is i have 1 to 20 records. then i insert 6534 digt as 21'st records:
digits on my records
1
2
3
..
20
6534

sum : 21 records

then i remove last record then insert record. new record give 6535.
how do i change it to new record give "21" digit? i am looking for way
where does "6534" has been save?

Drakla

After every delete you'd have to run

alter table yourtable auto_increment = 1

It'll automatically choose the next highest available number on the next insert

Roger_Ramjet

alirezaok wrote:
yes thanks.

the same problem is i have 1 to 20 records. then i insert 6534 digt as 21'st records:
digits on my records
1
2
3
..
20
6534

sum : 21 records

then i remove last record then insert record. new record give 6535.
how do i change it to new record give "21" digit? i am looking for way
where does "6534" has been save?

It has been saved in a system table that tracks and controls autoincrement fields. This table will contain the last (and therefore highest) value used in the autoinc filed. It then adds 1 to that value, stores it, and issued it as the next id for the next insert.

Drakla's solution is resetting the stored value to 1; only the system detects that there are existing values in the table so it looks up the highest and starts from there.

If you don't want any values missing from the sequence then you don't use autoinc, cos they will increment even if an insert fails.