mySQL glitch

[nsr81]

I'm having this strange problem with mySQL.

I created a table "emails" with three fields id(uniqe and auto increment int), email(varchar), date(uses now()).

I entered couple of dummy entries to check if everything was working correctly, after that I deleted those entries from the table using PHPmyAdmin. Now the id field had been incremented because of the dummy entries, the next item will get a number after the dummy entries even though they were deleted. e.g.

1. asdf@aslfd.com (date here)
2. asdfjl@lksajfl.com (date)
6. nsr81@visto.com (date)

how do I fill these gaps, to delete i used "delete from emails where id=#", is there something else i can do that next entry gets 4 not 7.

I hope I was clear enough for you to understand me.

[nsr81]

anyone?

come on guys, tell me something about it, can it be done: yes, no, maybe.

[reuben]

I'm not sure myself, so I am not able to answer. I'd wait a bit longer though before getting impatient. Everyone around here lives in different parts of the world, and most have jobs, etc.

[nsr81]

Sorry about that.

[reuben]

That's alright. I know it can get a bit frustrating when things don't work :D.

Currently I believe most people are at work though. Also, if I am not mistaken our younger clients should be starting back to school today, so they might not be around the boards until later either.

[Mike]

I am not to sure about MYSQL as I am an Oracle DBA but in Oracle you can issue an "alter sequence" command.

Check the MYSQL docs for a command like alter auto increment or alter sequence.

Keep us all informed as I plan on starting to use MYSQL in October, but I have my doubts.

[Mike]

I took a quick look at the MYSQL docs and it does not appear that the only alter statement available is "alter table". You can try the following options :

1 - create a copy of the table then delete the first table, then copy the data (not the auto number) back to the first table.

2 - manually rename the ids starting at 4

but why do you care if the primary key is not in order?

[nsr81]

Well I did it the hard way, I took th dump of structure and data of "emails" table with complete inserts through PHPmyAdmin, and droped the table. Then I just copy and paste from the dump to create the table and insert data. All I did was to ommit all the primary keys, so when inserting they all came in order.

Thanks for your help Mike and phpMyAdmin which made it a lot easier.

And to answer why do i care about the order: well there were two gaps in the primary key field. first when i created the table and I was testing how "now()" will behave. I took about three or four tries to figure that out. and the second gap was when I was testing to insert data from my webpage. I just wanted them to be in order so that in the future this little thing doesn't screw me over big time in some way(a lesson learned from my programming classes). I thought it would be better and easier if I fix it now since the table doesn't have much data in it.