Share This Article:

Subscribe to Kavoir:

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.
   
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.

Share This Article:

Subscribe to Kavoir:

You should also read:

• SQL: Randomly Shuffle Rows or Records – Reorder them in a random order
• MySQL: Update Multiple Rows or Records with One Single Query
• MySQL: Insert if doesn’t exist otherwise update the existing row
• MySQL: Deleting Duplicate Rows or Records | Selecting Unique Rows into A New Table
• SQL: CREATE TABLE … SELECT … to generate new table from existing tables