about auto_increment the primary key

[DEAMOTHUL the script keeper]

hi, when i have a database, like this:

personID               INTEGER AUTO_INCREMENT PRIMARY KEY    
personName          VARCHAR(50)
personLastName   VARCHAR(50)

suppose i put in 8 persons into this database, then after inserting those 8 persons suppose  i would delete them all again.
When i then start to add new persons again, the personID starts at 8 , but i want it to start at 1 again.

Can somebody tell me what i have to do to reset the auto_increment id  back to its begin point
So lets say i deleted 3 of the persons, the id would be set to 5, and so on.

thanks,

[Flash-db]

Not sure if this would be the best way - I'm sure there's a better solution out there.  But I think you can do something like:

CREATE TABLE sometable (tablecolumnName INT NOT NULL AUTO_INCREMENT PRIMARY KEY) AUTO_INCREMENT = someNumber

You can manually set  AUTO_INCREMENT = someNumber, but I'm not sure if this will be useful in your case?



- also for some reason I thought that auto increment started at the last number in the column before.  so say the auto incremented value of the last row was 5.  I thought it would then start with 6, but it sounds like that's not the case?

[Ronald Wernecke]

The Autoincrementnumber for a column is held in a system table and allways incrementing.
This actualy makes sense, 'cause when a table lives, it usualy mekse no sense to delete all and start again. Usualy you have something in the middle deleted and something new generated.
These autoincrement values are allways long integers, so it is rather impossible that thes values are running out of range for the lifetime of an application.

Renumbering makes non sense, 'cause you usualy use these keys (you can depend on it, that they are unique), are used as foreign keys. Thats actualy the reason for using them.

I beleive there is something like a reset function, but dont know for sure now.

[DEAMOTHUL the script keeper]

hi people, i hope there is a way to do it, cause else things will be pretty messed up

i hope there is someone out there that knows how to do it, im sure somebody else has had this problem before.

but thanks 4 the help , greatly appreciated

greetz

[Musicman]

Hi,

dont mess with the autoincrement numers .... if you really want all current entries listed as 1, 2, 3 just add those numbers to your listing (and keep the autoincrement ones for any links because they will not change; you would not like somebody to bookmark a page about person #3 and then come back to find out that person #3 is former person #7

Musicman

[DEAMOTHUL the script keeper]

hi musicman, but when something is deleted from the database the id is messed up.
what can i do about that?

greetz,

[Musicman]

Hi,

the id is not messed up, it is just unused (and will never be used again). So in the case of the bookmark, the script should just say: person xxx  left the database

Musicman

[DEAMOTHUL the script keeper]

thanks musicman,
so its best not to use the id field in  an ORDER BY method?

greetz,

[Ronald Wernecke]

Hi,
this autonumber function delivers you a save method of unique key values for to be used as foreign keys.
So, if you use them right, you mess up relations, if you change them.
Still the order of the keys is the order of how the records have been entered into the database. There are only "holes" in it, which show the deleted records.

If you want another order, you have to create your own field for it.

But if you only want ascending numbering of the current list, you can produce it in the script like line numbers.

I personaly don't see any sense in it, but erverybody as he likes

And if you only have a single table without any relations, you may omit such a field

[DEAMOTHUL the script keeper]

Ok thanks,