i have about 50 records in my database with field id as primary after some records have been deleted the numbers dont run in sequence eg 1 - 21 ok then 52 then 30-50 is there a way to renumber if you see what i mean cheers Doug
JustFuckingGoogleIt.com liewcf.com/blog/archives/2004/04/mysql-reset-auto-increament-number/ Reset Auto Increment Numbers A few ways of doing it. Hope that helps. If everything doesn't work, use Google. (Couldn't use live links, sorry xD)
i think u must manually readjust the values or write a query to insert the id values by turn or the primary key index
you don't have a back up? regardless, some hosting companys will auto back up your database everyday. check with them (open a suport ticket or whatever they want you to do lol) and see if they can restore it back to the way it was before things got screwed up.
Thats because of something called as table overhead. It typically occurs when you delete few rows. I have explained this in the past on my blog post The solution to your problem is, I assume you are using PHPmyAdmin: Go to the table structure of the table you wish and click on 'optimize table' link. There is no need to do anything of what has been suggested in previous posts because they could actually break your database.
Why you need them in sequence? Just use ORDER BY on id field to get the rows in sequence. You don't need to work if the table is not big enough.
sorted just ran this code and all done cheers Doug ALTER TABLE table_name DROP ID; OPTIMIZE TABLE table_name; ALTER TABLE table_name ADD ID BIGINT( 20 ) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST ; Code (markup):
^ Do you know what you have done? You have changed the id-data relationship in few records. That means that if something was previously available for id 55, it will now be available for id 54. in short, you have broken few of your relations and links. There was no need to drop any column. You just needed to optimize it
thanks for your reply i can see what you mean but i tried optimize but it did not work for some reason i had about 40 records numberd 1 to 40 but in the middle i had one num 42 so from what you say if every time a record is deleted if i optimize all should be ok cheers Doug