We help IT Professionals succeed at work.

How to edit MySQL table

814 Views
Last Modified: 2013-12-12
Using phpmyadmin I have created a new field in my table and I've called it id. I cannot autoincrement. When I try I get the following error:

ALTER TABLE users ADD id INT  not null AUTO_INCREMENT FIRST
MySQL said: Incorrect table definition; there can be only one auto column and it must be defined as a key

Currently the table field email is primary and unique.

If I add a field id to my table, what sql statement can I write that would open each record and edit id such that I end up with a sequence of numbers for each record - 1, 2, 3, 4, 5, etc.

Thank You
Comment
Watch Question

CERTIFIED EXPERT

Commented:
Did you have a field set to auto-increment before you added the id field?
Was there a primary key set?

What I would do is

1) Make a backup of the database in case something goes wrong at least I can get it back to the previous state.

2) Modify the table; unset the primary key and the auto-increment.

3) Add the new field and set the auto-increment and primary key to the new field.

Commented:
The problem is that you can only have 1 primary ket per table.

Change the email to just unique, and then add the autonumber field (which would automatically become primary)

Author

Commented:
Is there a way for me to fill ID all the while keeping my other fields as they are? I'd rather not sit here and input sequencial numbers by hand.

Author

Commented:
??
CERTIFIED EXPERT

Commented:
Do you have access to php? Could probably write a script to query the db and update it with a loop to increment the number.

Author

Commented:
Indeed, I've been using phpmyadmin to interface with mysql. What to do next, I don't know.
Can you send the output of "show create table tablename

Author

Commented:
Database bcs
Error
SQL-query: [Edit]

show create table tablename
MySQL said: Table 'bcs.tablename' doesn't exist
Actualll...

you need to put your table name in the query...

show create table thetablewearecouriousabout;

Commented:
"Is there a way for me to fill ID all the while keeping my other fields as they are? I'd rather not sit here and input sequencial numbers by hand."

If you've set the new ID field as an autonumber (a drop down option in phpMyAdmin), then it will automatically create the ID numbers - you don't have to fill this in by hand.

Just remember to remove the "primary" property from the email field first, otherwise it wont work.

Commented:
By the way, don't forget to set the new column as primary at the same time as autonumber. Otherwise creating the column will fail.

Author

Commented:
I dropped email as primary. Then I tried adding id as autoincrement and this is the error I received:

Error
SQL-query: [Edit]

ALTER TABLE users ADD id INT  not null AUTO_INCREMENT FIRST
MySQL said: Incorrect table definition; there can be only one auto column and it must be defined as a key

Author

Commented:
The bottom line is that I want to keep email as primary and unique. I just want to fill id with an incremental number. there must be some sort of sql statement that I can execute that will do this. no?

Commented:
Unless you want to write in an autonumber into every SQL query you create - it's just not a good idea.

Why is it so important to have the mail field as primary and unique (you shouldn't use both on a single field anyway)?

Here's the code:
ALTER TABLE `users` DROP PRIMARY KEY;
 
ALTER TABLE `users` ADD `id` INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

Open in new window

Author

Commented:
After I get the numbers into the id field, how do I get email back to how it was (as primary and unique)?

I'm not sure why it's important. This is the way my content management system was programmed and I trust that the programmers did this intentionally and that it should remain that way. I just want to get a 1 through 400 number listed in an id field for each record in the database.
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
Forced accept.

Computer101
EE Admin
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.