Add Auto-Increment ID to existing table

Question

I have a pre-existing table  containing  fname    lname    email    password  and  ip   But now I want an auto-increment column  However  when I enter   ALTER TABLE users ADD id int NOT NULL AUTO INCREMENT   I get the following    1075 - Incorrect table definition  there can be only one auto column and it must be defined as a key   Any advice

User · Answer

If you don t care whether the auto-id is used as PRIMARY KEY  you can just do  ALTER TABLE  myTable  ADD COLUMN  id  INT AUTO INCREMENT UNIQUE FIRST    I just did this and it worked a treat

User · Answer

Just change the ADD to MODIFY and it will works    Replace   ALTER TABLE users ADD id int NOT NULL AUTO INCREMENT   To   ALTER TABLE users MODIFY id int NOT NULL AUTO INCREMENT

User · Answer

Proceed like that    Make a dump of your database first  Remove the primary key like that   ALTER TABLE yourtable DROP PRIMARY KEY  Add the new column like that   ALTER TABLE yourtable add column Id INT NOT NULL AUTO INCREMENT FIRST  ADD primary KEY Id Id   The table will be looked and the AutoInc updated

User · Answer

ALTER TABLE users ADD id int NOT NULL AUTO INCREMENT primary key FIRST

User · Answer

Try this ALTER TABLE  users  ADD  id  INT NOT NULL AUTO INCREMENT   for an existing primary key

User · Answer

Delete the primary key of a table if it exists    ALTER TABLE  tableName  DROP PRIMARY KEY    Adding an auto-increment column to a table    ALTER TABLE  tableName  ADD  Column name  INT PRIMARY KEY AUTO INCREMENT    Modify the column which we want to consider as the primary key   alter table  tableName  modify column  Column name  INT NOT NULL AUTO INCREMENT PRIMARY KEY

User · Answer

ALTER TABLE users CHANGE id int  30   NOT NULL AUTO INCREMENT   the integer parameter is based on my default sql setting have a nice day

User · Answer

If you run the following command    ALTER TABLE users ADD id int NOT NULL AUTO INCREMENT PRIMARY KEY    This will show you the error    ERROR 1060  42S21   Duplicate column name  id    This is because this command will try to add the new column named id to the existing table   To modify the existing column you have to use the following command    ALTER TABLE users MODIFY id int NOT NULL AUTO INCREMENT PRIMARY KEY    This should work for changing the existing column constraint

User · Answer

Well  you must first drop the auto increment and primary key you have and then add yours  as follows   -- drop auto increment capability alter table  users  modify column id INT NOT NULL  -- in one line  drop primary key and rebuild one alter table  users  drop primary key  add primary key id   -- re add the auto increment capability  last value is remembered alter table  users  modify column id INT NOT NULL AUTO INCREMENT

User · Answer

This SQL request works for me   ALTER TABLE users CHANGE COLUMN  id   id  INT 11  NOT NULL AUTO INCREMENT

User · Answer

ALTER TABLE  table  ADD  id  INT NOT NULL AUTO INCREMENT unique   Try this  No need to drop your primary key

User · Answer

For PostgreSQL you have to use SERIAL instead of auto increment   ALTER TABLE your table name ADD COLUMN id SERIAL NOT NULL PRIMARY KEY

User · Answer

Check for already existing primary key with different column  If yes  drop the primary key using    ALTER TABLE Table1 DROP CONSTRAINT PK Table1 Col1 GO   and then write your query as it is

User · Answer

If you want to add AUTO INCREMENT in an existing table  need to run following SQL command    ALTER TABLE users ADD id int NOT NULL AUTO INCREMENT primary key

User · Answer

First you have to remove the primary key of the table  ALTER TABLE nametable DROP PRIMARY KEY   and now yo can add the autoincrement      ALTER TABLE nametable ADD id INT NOT NULL AUTO INCREMENT PRIMARY KEY

[mysql] Add Auto-Increment ID to existing table?

Examples related to mysql

Examples related to sql

Examples related to definition

Examples related to auto-increment