MySQL: AUTO_INCREMENT on existing tables.

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

MySQL: AUTO_INCREMENT on existing tables.

Post 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?
User avatar
jaoudestudios
DevNet Resident
Posts: 1483
Joined: Wed Jun 18, 2008 8:32 am
Location: Surrey

Re: MySQL: AUTO_INCREMENT on existing tables.

Post by jaoudestudios »

Are you in phpMyAdmin or sql command line?
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: MySQL: AUTO_INCREMENT on existing tables.

Post 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?
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: MySQL: AUTO_INCREMENT on existing tables.

Post 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.
(#10850)
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: MySQL: AUTO_INCREMENT on existing tables.

Post 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.
User avatar
JellyFish
DevNet Resident
Posts: 1361
Joined: Tue Feb 14, 2006 7:18 pm
Location: San Diego, CA

Re: MySQL: AUTO_INCREMENT on existing tables.

Post 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
User avatar
andyhoneycutt
Forum Contributor
Posts: 468
Joined: Wed Aug 27, 2008 10:02 am
Location: Idaho Falls

Re: MySQL: AUTO_INCREMENT on existing tables.

Post 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
User avatar
Bill H
DevNet Resident
Posts: 1136
Joined: Sat Jun 01, 2002 10:16 am
Location: San Diego CA
Contact:

Re: MySQL: AUTO_INCREMENT on existing tables.

Post 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.
Post Reply