ALTER table - adding AUTOINCREMENT in MySQL

Question

I created a table in MySQL with on column itemID  After creating the table  now I want to change this column to AUTOINCREMENT  How can this be done using ALTER statements  Table definition  ALLITEMS  itemid int 10  unsigned  itemname varchar 50   I am using the following code but it is throwing a syntax error Error  syntax incorrect  ALTER TABLE allitems MODIFY itemid INT 10  UNSIGNED AUTOINCREMENT

User · Answer

ALTER TABLE tblcatalog     CHANGE COLUMN id id INT 11  NOT NULL AUTO INCREMENT FIRST

User · Answer

CREATE TABLE ALLITEMS      itemid INT 10 UNSIGNED      itemname VARCHAR 50      ALTER TABLE ALLITEMS CHANGE itemid itemid INT 10 AUTO INCREMENT PRIMARY KEY   DESC ALLITEMS   INSERT INTO ALLITEMS itemname  VALUES       Apple          Orange          Banana     SELECT       FROM     ALLITEMS    I was confused with CHANGE and MODIFY keywords before too   ALTER TABLE ALLITEMS CHANGE itemid itemid INT 10 AUTO INCREMENT PRIMARY KEY   ALTER TABLE ALLITEMS MODIFY itemid INT 5     While we are there  also note that AUTO INCREMENT can also start with a predefined number   ALTER TABLE tbl AUTO INCREMENT   100

User · Answer

Basic syntax for adding an AUTO INCREMENT PRIMARY KEY to the OP s existing table   ALTER TABLE allitems MODIFY itemid INT 10  UNSIGNED AUTO INCREMENT PRIMARY KEY    Or for a new table  here s the syntax example from the docs   CREATE TABLE animals        id MEDIUMINT NOT NULL AUTO INCREMENT       name CHAR 30  NOT NULL       PRIMARY KEY  id       Traps and things to note    An AUTO INCREMENT column must have an index on it   Usually  you ll want it to be the PRIMARY KEY  but MySQL does not require this   It s usually a good idea to make your AUTO INCREMENT columns UNSIGNED  From the docs      Use the UNSIGNED attribute if possible to allow a greater range    When using a CHANGE or MODIFY clause to make a column AUTO INCREMENT  or indeed whenever you use a CHANGE or MODIFY clause  you should be careful to include all modifiers for the column  like NOT NULL or UNSIGNED  that show up in the table definition when you call SHOW CREATE TABLE yourtable  These modifiers will be lost otherwise

User · Answer

ALTER TABLE allitems CHANGE itemid itemid INT 10  AUTO INCREMENT

User · Answer

The syntax         ALTER TABLE  table1  CHANGE  itemId   itemId  INT  11   NOT NULL AUTO INCREMENT    But the table needs a defined key  ex primary key on itemId

User · Answer

ALTER TABLE t name modify c name INT 10  AUTO INCREMENT PRIMARY KEY

User · Answer

ALTER TABLE  ALLITEMS      CHANGE COLUMN  itemid   itemid  INT 10  UNSIGNED NOT NULL AUTO INCREMENT

[mysql] ALTER table - adding AUTOINCREMENT in MySQL

Examples related to mysql

Examples related to sql

Examples related to alter-table