ALTER table - adding AUTOINCREMENT in MySQL
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 ALT开发者_运维技巧ER 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;
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).
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:
- 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
columnsUNSIGNED
. From the docs:Use the UNSIGNED attribute if possible to allow a greater range.
- When using a
CHANGE
orMODIFY
clause to make a columnAUTO_INCREMENT
(or indeed whenever you use aCHANGE
orMODIFY
clause) you should be careful to include all modifiers for the column, likeNOT NULL
orUNSIGNED
, that show up in the table definition when you callSHOW 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;
ALTER TABLE t_name modify c_name INT(10) AUTO_INCREMENT PRIMARY KEY;
精彩评论