ALTER table - adding AUTOINCREMENT in MySQL
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;
ALTER TABLE `ALLITEMS`
CHANGE COLUMN `itemid` `itemid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;
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).