filibuster1015
asked on
How to edit MySQL table
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
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
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)
Change the email to just unique, and then add the autonumber field (which would automatically become primary)
ASKER
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.
ASKER
??
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.
ASKER
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
ASKER
Database bcs
Error
SQL-query: [Edit]
show create table tablename
MySQL said: Table 'bcs.tablename' doesn't exist
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
you need to put your table name in the query...
show create table thetablewearecouriousabout
"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.
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.
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.
ASKER
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
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
ASKER
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?
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:
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;ASKER
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Forced accept.
Computer101
EE Admin
Computer101
EE Admin
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.