Home » SQL

SQL Query to auto increment primary key

In this article, we are going to learn about the auto increment function of SQL and then the process to add it on our primary key.
Submitted by Manu Jemini, on March 14, 2018

Primary keys are important because they have unique records which then used to identify the records in the table. But, how do we know which the unique value is and which is not?

There is no easy way we can predict the next unique value without looking in the database. So, this brings a unique concept which let the database handles the value of a primary key.

The database keeps track of the maximum value our key has reached in the past and it always increments that value by 1. When we let the database handles the key, we should give any value for that field when we insert a record.

Now to make a primary key AUTO_INCREMENT, we have to have a field which has the datatype with arithmetic capabilities. For sure, varchar isn’t that datatype.

If we got this thing in order then just ALTER the table and MODIFY the column.

Table (employee) having three fields and four data in it,

    Id		Name		Address

    100		Aman		Mumbai
    200		Arun		Pune
    300		Karan		Delhi
    400		Aman		Mumbai

Above table got three columns and one of them (id) is our primary key and now we just need to create an alter table query with modify column and then give the column name on which we need to add auto increment after that just add AUTO_INCREMENT in it.

Query:

ALTER TABLE employee 
MODIFY COLUMN 'id' INT(10) UNSIGNED NOT NULL AUTO_INCREMENT;

Output