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;
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;
CHANGE and MODIFY COLUMN?
UNSIGNED in the CHANGE clause, you turn itemid into a SIGNED int and thus reduce its maximum value.
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).
ex primary key and primary key on itemid?ALTER TABLE `ALLITEMS`
CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
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 allitems
CHANGE itemid itemid INT(10) AUTO_INCREMENT;
ALTER TABLE tblcatalog
CHANGE COLUMN id id INT(11) NOT NULL AUTO_INCREMENT FIRST;
ALTER TABLE employees CHANGE id id int AUTO_INCREMENT PRIMARY KEY;
To reset the value of auto increment:
ALTER TABLE [table_name] AUTO_INCREMENT = start_value;
ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;
AUTOINCREMENThas an underscore:AUTO_INCREMENT....