Auto Increment Problem After Removing Data From Table

[sundawg]

Here is a table I created in my databse, id_num is my auto_increment...

CREATE TABLE main_news (
id_num int(5) NOT NULL AUTO_INCREMENT,
title VARCHAR(70),
submit_date DATE NOT NULL,
author VARCHAR(30),
submit_time TIME NOT NULL,
message TEXT NOT NULL,
PRIMARY KEY(id_num) )

When I add data to the database the data is going in and since this is my first time entering, id_num is = 1

But if I use this command to say remove the data from the database cuz I don't want it anymore:

DELETE FROM main_news WHERE id_num=1

it deletes it, which is good but when I enter in my next piece of data, id_num is equal to 2!!! It is not seeing that 1 is missing and "auto increments" itself. When I pull data from the database for displaying purposes in the future, will this cause a problem?

Or should I be checking to see if there is an id_num = 1? or any other id_num that I decide to delete. I want to be able to add and remove and edit these news articles that I am putting into this database and then display them on another page...

Like when I pull data I have to move via the auto increment don't I? If 1 or 3 or 57 are missing (for example) will that cause errors in my search through my database to increment through it to find data to display on a page (ie increment through and find posts made during a certain time span)?

Do I have to reset this auto increment when I remove 1 or more from it?

I am still learning about this and need some answers =0)

Thanks to anyone that can help me!

Sundawg

[thorpe]

it shouldn't cause any problems. as long as your not putting your records in order by the auto_inc field. which by the way... you shouldn't be.

the auto_inc filed is responisble for making unique id's, not putting your records in order.

[Drakla]

Plus it's a very VERY good thing that the autoincrement doesn't reset when you delete fields. Imagine you have a users table and a payments table with a userid as a foreign key - you delete a user, haven't set it up to delete their payments, then put in a new user who gets their id - already sounding catastrophic?

If you truncate a table it will reset the auto increment value, and you can set it with alter table, but really think about it before hand.

[sundawg]

Thanks guys.. sounds like I am just letting the auto increment do its thing and not worrying about it =0)

Sundawg