ALTER table – adding AUTOINCREMENT in MySQL

by 
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;

More Related Contents:

  1. ALTER TABLE to add a composite primary key
  2. If two columns are 1 then make new column
  3. MySQL – Get row number on select
  4. Group by clause in mySQL and postgreSQL, why the error in postgreSQL?
  5. How can I tell when a MySQL table was last updated?
  6. Cast from VARCHAR to INT – MySQL
  7. Operand Should Contain 1 Column – MySQL NOT IN
  8. Is it possible to query a tree structure table in MySQL in a single query, to any depth?
  9. MySQL: Error Code: 1118 Row size too large (> 8126). Changing some columns to TEXT or BLOB
  10. What’s the difference between comma separated joins and join on syntax in MySQL? [duplicate]
  11. Adding multiple columns AFTER a specific column in MySQL
  12. Determine Rank based on Multiple Columns in MySQL
  13. Best practices for SQL varchar column length [closed]
  14. How to get the count of each distinct value in a column? [duplicate]
  15. How to enable INNODB in mysql
  16. Bash script to insert values in MySQL
  17. What are differences between INSERT and UPDATE in MySQL?
  18. How to produce “phantom read” in REPEATABLE READ? (MySQL)
  19. How do I lag columns in MySQL?
  20. How to get multiple records against one record based on relation?
  21. How to regex in a MySQL query
  22. When is a timestamp (auto) updated?
  23. MySQL Group By Hours
  24. RMySQL dbWriteTable with field.types
  25. Syntax error on MERGE statement
  26. `MODIFY COLUMN` vs `CHANGE COLUMN`
  27. INSERT INTO Table from multiple tables
  28. Execute INSERT if table is empty?
  29. Mysql How do you create a clustered index?
  30. How do I extract Month and Year in a MySQL date and compare them?

Leave a Comment