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;
This question is related to
mysql
sql
alter-table
ALTER TABLE allitems
CHANGE itemid itemid INT(10) AUTO_INCREMENT;
ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;
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).
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:
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.)AUTO_INCREMENT
columns UNSIGNED
. From the docs:
Use the UNSIGNED attribute if possible to allow a greater range.
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.ALTER TABLE tblcatalog
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE `ALLITEMS`
CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
Source: Stackoverflow.com