MySQL and 0 for Auto_increment?!

Suppose the following table:

CREATE TABLE categories (
id int auto_increment not null,
parent_id int not null,
name varchar(25) not null,
description varchar(255) not null,
PRIMARY KEY (id),
INDEX parent_id (parent_id),
INDEX name (name)
);

I want The first record "id" field to take a value = 0. However, MySQL do something else, it
assign the value = 1 to it.
INSERT INTO categories (id, parent_id, name,description)
VALUES (0, 0, 'Top', 'This is the top level category.');

Ok, I update the value of "id" to be set as 0 as follows

UPDATE categories SET id = 0 WHERE id = 1;

Wow, That's so nice.

The problem starts with the second record as follows

INSERT INTO categories (name, description)
VALUES ('Fruits', 'Fresh and tasty fruits.');

MySQL seems to have very strong memory ! It can not forget that the the first record "id" field
has the value 1, on the other hand it forgets it becomes 0!. The second record "id" field value
takes the value 2, inspite of I respect it to be 1.

The question is: How can I solve this problem in MySQL?
Notice: The solution I respect should not need any use of MySQL function.

I have MySQL Ver 4.0.12 on windows ME.
This situation is from a tutorial named as Building an E-Commerce Site Part 1: Building a
Product Catalog
By Ying Zhang
On Developer Shed website.

Please Help Quickly

Auto incrementing columns are to be used to have a unique identifier. Another problem you are going to run into is that if you ever delete the last item in the table, mysql will NOT reassign that value. It will skip over it.

This is by design. To do what you want, I would suggest not using auto incrementing values, and just manually assign the id, starting with 0. Then when you insert a new row, find the max value and insert one higher.

yes. mysql autoincrement field cant have a value of 0.

if you write a query like

INSERT INTO table SET id=0, name='blah'

that is the same as writting

INSERT INTO table SET name='blah'

(without the id field), so then mysql sets id field to the next autoincrement value...


there is no way around that..

I'm sorry for that mistake. I believed my table has auto_increment id column with id=0. Which was true but it was not auto_increment.