Categories
SQL / MySQL Tips and Tutorials

Create SQL Auto Increment Column (ID: Primary Key) in Table

With database development, a perfect data structure paradigm is that data records should all be uniquely distinguishable from each other. Therefore when you are designing and creating data fields or columns for an entity, you must always have an identifying column or field for each and every of the records stored in a table, enabling the identification of any record upon finding the unique value.

We usually call it the ID column. And it’s made auto incremental so that whenever there’s a new record or data entry inserted (INSERT INTO), its value of this column is auto incremented by 1 after the max of the existing values.

Not only that, after it’s auto incremented upon every insertion, it’s also made the Primary Key of the table. In essence, a primary key column contains the unique ID for each record, facilitating data indexing.

Follow me with MySQL and a faked table ‘workers’ as example for how to create, modify or add auto incremented field in SQL.

SQL Auto Increment Creation

You have at least 2 approaches for creating an auto increment column:

  1. With phpMyAdmin which most hosting providers come with, just make sure you have selected auto_increment for the column. Preferably you would also check it as a Primary Key column.
    auto_increment & primary key
  2. With manual SQL table creation, append ‘AUTO_INCREMENT PRIMARY KEY‘ in the column description:
    CREATE TABLE `workers` (
    	`id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ,
    	`name` VARCHAR( 255 ) NOT NULL ,
    	`age` TINYINT UNSIGNED NOT NULL ,
    )

Alter a column to be auto increment

To modify an existing integer column to be auto incremented in an existing table:

  1. Either edit the column via table Structure panel in phpMyAdmin,
  2. Or run the following SQL query:
    ALTER TABLE `workers` CHANGE `id` `id` SMALLINT( 5 ) UNSIGNED NOT NULL AUTO_INCREMENT

    One note thought, that it’s a must to include all previous attributes (such as SMALLINT(5), UNSIGNED and NOT NULL) of the column if you do not want to annul them after the alteration.

    Very probably, if you are making an ID column that can be indexed as primary key, you would want to append one more attribute: PRIMARY KEY.

Create a new column to be auto increment

Any table without an auto incremental column that is the primary key is just not healthy. It’s data butcher. You’ve got to have one for all of them, buddy.

  1. Either adding a new column and specify auto_increment for Extra in table Structure panel in phpMyAdmin,
  2. Or run the following SQL query for an existing table faked as ‘workers’:
    ALTER TABLE `workers` ADD `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY ;

    PRIMARY KEY at the end of the query is not essential if you also want to make the new column to be an ID column.

By Yang Yang

Hello, I'm Yang. I build online businesses that please people. Want to join in and post some useful articles on Kavoir.com? Shoot me a message.

5 replies on “Create SQL Auto Increment Column (ID: Primary Key) in Table”

How cud i use auto increment for string p101………..i wud like to have ‘p’ at the beginning and with an auto increment intger followed….??????
Is there any ways to handle this???Please reply

You might note that when adding an ID column, most people would like to have it at the beginning of the column list. This is accomplished by simply adding FIRST to the end of the ALTER TABLE statement:
ALTER TABLE `workers` ADD `id` SMALLINT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

the column is added successfully but when I am inserting data with command
it is giving me error
ERROR 1136 (21S01): Column count doesn’t match value count at row 1
what is the problem ?

Comments are closed.