MySQL Auto Increment Problem

[wfernley]

Hello I seem to be having a problem with one of my Tables in MySQL.

I have an Auto Increment set up but for some reason it doesn't count up from the last object inside.

I have 3 objects inside right now.

+----+-----------+--------------+---------------------------------+
¦ id ¦ parent_id ¦ name ¦ description ¦
+----+-----------+--------------+---------------------------------+
¦ 0 ¦ 0 ¦ Top ¦ This is the top level category. ¦
¦ 1 ¦ 0 ¦ Motherboards ¦ ¦
¦ 6 ¦ 0 ¦ sub ¦ sub cat ¦
+----+-----------+--------------+---------------------------------+

I want the first one "Top" to have an id of 0 and then "Motherboards" to have an id of 1. From there I want it to count up from 1. For some reason it remembers objects that were deleted and counts up from there. Because of this it skips 2 and goes to 6. If I delete "Sub" and add a new one, the id will be set to "7".

I have looked all over the MySQL site but still have been unable to find a fix, unless "Top" would be set to 1 and count from there.

Anyone know a fix to this?

Thanks in advance. :)

Wes

[txbakers]

When you delete a record in mySql it still retains a record of that slot being filled. In fact, most real databases do this.

You have to run another command over the database to reclaim the missing slots. I can't remember what it's called at the moment, but it's in the mySql documentation.

[Fischerlaender]

ALTER TABLE table_name AUTO_INCREMENT = 27

This command sets the auto_increment value to 27.
[mysql.com...]

[wfernley]

Hi thanks for the post :)

I have tried that actually, and tried setting it to 0, but it starts at 1 still. :S kinda weird.

Thanks again.

Wes

[Fischerlaender]

Zero is a special value, which shouldn't be used in an AUTO_INCREMENT column. E.g. if you run CHECK TABLE on your table, it will complain about 0 values in an AUTO_INCREMENT column. I think this has several reasons, one of them is that zero and NULL are sometimes not used the way they should.

[wfernley]

Yeah I think it is a big hassle. The tutorial I am doing uses 0. I have made changes in the code though now so it counts from 2 and up. And instead of the first value have 0 it now has 1. Simple, less problems. :)

Thanks for your help.

Wes