Page 1 of 1

MySQL: AUTO_INCREMENT on existing tables.

Posted: Tue Sep 02, 2008 8:22 pm
by JellyFish
I have an existing table. I just added a new field to this table called id. This new field is typed INT. How do I add AUTO_INCREMENT to this new field?

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Wed Sep 03, 2008 2:16 am
by jaoudestudios
Are you in phpMyAdmin or sql command line?

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Wed Sep 03, 2008 3:45 pm
by JellyFish
jaoudestudios wrote:Are you in phpMyAdmin or sql command line?
I'm in phpMyAdmin, but doesn't phpMyAdmin have a command line in it anyway?

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Wed Sep 03, 2008 3:52 pm
by Christopher
phpMyAdmin has a selection for autoincrement when you create the column. You may be able to modify the column and add autoincrement. But you may need to delete and recreate it.

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Wed Sep 03, 2008 5:23 pm
by JellyFish
How? When I go to create a new column in a table AUTO_INCREMENT is nowhere to be found. I'm using Startfield phpMyAdmin.

Also, if I could do it with an SQL statement I could just run it in the command line thingy.

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Wed Sep 03, 2008 5:54 pm
by JellyFish
Aha!

Code: Select all

ALTER TABLE `chart_room` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT FIRST, ADD PRIMARY KEY (id);
This works. Except it doesn't give the exact behavior I want. Every time I add a new row to this table the id auto increments, which is want I want it to do. When I delete and then add a new row that one that was deleted waste that number forever and no new row will every be able to replace it.

Will this cause an eventual situation where I have not the amount of rows as of an unsigned integer(4294967295). I hope you understand what I mean. If you don't then I will illustrate.

If I have 20 rows in my table, then each row will have a number from 1-20 in the id field respectively; the first row will have 1, the second will have 2, etc. If I delete row 11 and then add a new row, that new row will have the number 21 in the id field/column, rather then 11. You can see that I have still 20 rows (because I deleted one before I added a new one) but I've used 21 unique id numbers already. As I add and delete rows this will allocate till I've gotten to the id's data type limit of 4294967295 (unsigned integers are whole numbers from 0 to 4294967295).

To you see the problem that I am delineating? Is this really an issue or once the the id field has reached 4294967295 will it then start back at 0 and then from there find a number available?

EDIT: I found this. It is the exact issue I'm having. The solution is good, only if all the row's ids are contiguous.

So I've decided not to use a unique ID field in my table until I get this problem straighten out. :D

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Wed Sep 03, 2008 11:59 pm
by andyhoneycutt
JellyFish wrote:...When I delete and then add a new row that one that was deleted waste that number forever and no new row will every be able to replace it.
Such is the life of unsigned integers. I wouldn't worry about it: if you're terribly concerned- alter table chat modify id bigint; - because they are just numbers.
JellyFish wrote:Will this cause an eventual situation where I have not the amount of rows as of an unsigned integer(4294967295). I hope you understand what I mean. If you don't then I will illustrate.
That's great expectations man. Worst case scenario, reset the next value to increment from: ALTER TABLE chat AUTO_INCREMENT=1[/quote]
JellyFish wrote:So I've decided not to use a unique ID field in my table until I get this problem straighten out. :D
All I can say is not having a unique ID field as a primary key would drive me insane. I think you'll find you can do exactly what you're looking to do and retain your current database schema.

-Andy

Re: MySQL: AUTO_INCREMENT on existing tables.

Posted: Fri Sep 05, 2008 8:57 am
by Bill H
When you are showing the table, select a field for revision. One of the items will be a dropdown named "Extra." the selection in that dropdown is "auto-increment."
To you see the problem that I am delineating? Is this really an issue or once the the id field has reached 4294967295 will it then start back at 0 and then from there find a number available?
Making 1 entry per second continuously, 24/7/365, I think it will take about 136 years to reach that level, so I doubt it will become a problem in your lifetime.
If you fear that it is you could go to a BIGINT and get 18446744073709551615 entries.