Alter a MySQL column to be AUTO INCREMENT

Question

I   m trying to modify a table to make its primary key column AUTO INCREMENT after the fact   I have tried the following SQL  but got a syntax error notification   ALTER TABLE document ALTER COLUMN document id AUTO INCREMENT   Am I doing something wrong or is this not possible     --------------------    VERSION               --------------------    5 0 75-0ubuntu10 2    --------------------

User · Accepted Answer

ALTER TABLE document MODIFY COLUMN document id INT auto increment

User · Answer

alter table tbl user MODIFY COLUMN id int 10  auto increment

User · Answer

You can apply the atuto increment constraint to the data column by the following query  ALTER TABLE customers MODIFY COLUMN customer id BIGINT NOT NULL AUTO INCREMENT   But  if the columns are part of a foreign key constraint you  will most probably receive an error  Therefore  it is advised to turn off foreign key checks by using the following query  SET foreign key checks   0   Therefore  use the following query instead  SET foreign key checks   0  ALTER TABLE customers MODIFY COLUMN customer id BIGINT NOT NULL AUTO INCREMENT  SET foreign key checks   1

User · Answer

Since SQL tag is attached to the question I really think all answers missed one major point  MODIFY command does not exist in SQL server So you will be getting an error when you run the ALTER TABLE Satellites MODIFY COLUMN SatelliteID INT auto increment PRIMARY KEY   In this case you can either add new column as INT IDENTITY ALTER TABLE Satellites    ADD ID INT IDENTITY        CONSTRAINT PK YourTable PRIMARY KEY CLUSTERED   OR  Fill the existing null index with incremental numbers using this method  DECLARE  id INT SET  id   0  UPDATE Satellites SET  id   SatelliteID    id   1

User · Answer

You must specify the type of the column before the auto increment directive  i e  ALTER TABLE document MODIFY COLUMN document id INT AUTO INCREMENT

User · Answer

In my case it only worked when I put not null  I think this is a constraint   ALTER TABLE document MODIFY COLUMN document id INT NOT NULL AUTO INCREMENT

User · Answer

Below statement works  Note that you need to mention the data type again for the column name  redeclare the data type the column was before    ALTER TABLE document MODIFY COLUMN document id int AUTO INCREMENT

User · Answer

AUTO INCREMENT is part of the column s datatype  you have to define the complete datatype for the column again   ALTER TABLE document MODIFY COLUMN document id int AUTO INCREMENT    int taken as an example  you should set it to the type the column had before

User · Answer

AUTO INCREMENT is part of the column s datatype  you have to define the complete datatype for the column again   ALTER TABLE document ALTER COLUMN document id int AUTO INCREMENT    int taken as an example  you should set it to the type the column had before

User · Answer

Setting column as primary key and auto increment at the same time     mysql gt  ALTER TABLE persons MODIFY COLUMN personID INT auto increment PRIMARY KEY      Query OK  10 rows affected  0 77 sec      Records  10  Duplicates  0  Warnings  0      mysql gt

User · Answer

Previous Table syntax   CREATE TABLE apim log request  TransactionId varchar 50  DEFAULT NULL     For changing the TransactionId to auto increment use this query  ALTER TABLE apim log request MODIFY COLUMN TransactionId INT auto increment

User · Answer

To modify the column  in mysql we use alter and modify keywords  Suppose we have created a table like   create table emp      id varchar 20       ename varchar 20       salary float      Now we want to modify type of the column id to integer with auto increment  You could do this with a command like   alter table emp modify column id int 10  auto increment

User · Answer

The SQL to do this would be   ALTER TABLE  document  MODIFY COLUMN  document id  INT AUTO INCREMENT    There are a couple of reasons that your SQL might not work   First  you must re-specify the data type  INT in this case    Also  the column you are trying to alter must be indexed  it does not have to be the primary key  but usually that is what you would want    Furthermore  there can only be one AUTO INCREMENT column for each table   So  you may wish to run the following SQL  if your column is not indexed     ALTER TABLE  document  MODIFY  document id  INT AUTO INCREMENT PRIMARY KEY    You can find more information in the MySQL documentation  http   dev mysql com doc refman 5 1 en alter-table html for the modify column syntax and http   dev mysql com doc refman 5 1 en create-table html for more information about specifying columns

User · Answer

Use the following queries   ALTER TABLE YourTable DROP COLUMN IDCol  ALTER TABLE YourTable ADD IDCol INT IDENTITY 1 1

User · Answer

You can use the following query to make document id to increment automatically  ALTER TABLE document MODIFY COLUMN document id INT auto increment   It is preferred to make document id primary key as well  ALTER TABLE document MODIFY COLUMN document id INT auto increment PRIMARY KEY

User · Answer

Try the following  ALTER TABLE table name MODIFY COLUMN id datatype auto increment

User · Answer

You can do it like this      alter table  table name  modify column  column name   column type  AUTO INCREMENT

User · Answer

If none of  the above works try this  This is what I did in MYSQL and yes  you need to write the column name  document id  twice    ALTER TABLE document CHANGE COLUMN document id document id INT 11  NOT NULL AUTO INCREMENT

User · Answer

As you are redefining the column again  you have to specify the datatype again and add auto increment to it as it s a part of datatype   ALTER TABLE  document  MODIFY COLUMN  document id  INT AUTO INCREMENT

User · Answer

Just like this   alter table document modify column id int 11  auto increment

User · Answer

Roman is right  but note that the auto increment column must be part of the PRIMARY KEY or a UNIQUE KEY  and in almost 100  of the cases  it should be the only column that makes up the PRIMARY KEY    ALTER TABLE document MODIFY document id INT AUTO INCREMENT PRIMARY KEY

[sql] Alter a MySQL column to be AUTO_INCREMENT

Examples related to sql

Examples related to mysql

Examples related to alter-table