AUTO_INCREMENT attribute in MySQL | Auto incrementing a column data

Creating Sequence using AUTO_INCREMENT

In MySQL, the sequence can be generated using the AUTO_INCREMENT for a column. In MySQL, the sequence is a list of integers generated in ascending order. The sequence is used for a particular column in a table that contains unique values. For example, the registration number of students in a college is always unique and AUTO_INCREMENT can be used for this.

There are certain rules which are applied for AUTO_INCREMENT attribute.

1. Each table can have only one AUTO_INCREMENT column and the data type of that column should be an integer.
2. The NOT NULL constraint is automatically added by MySQL to an AUTO_INCREMENT column.
3. The AUTO_INCREMENT column must be indexed.

For example:

CREATE TABLE students(
reg_no INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
stram VARCHAR(255)
);

On execution of the above query:

• MySQL will create a table named students.
• Three columns will be added to the table named reg_no, name, and stream.
• AUTO_INCREMENT is added to the column reg_no whose data type is an integer and is the primary key of the table.

How does AUTO_INCREMENT Works?

• The starting value of the 1st row in a AUTO_INCREMENT column is 1 and it increases by 1 for every subsequent row when we insert a row having value NULL or omit the value for that column.
• The last generated sequence number can be obtained using the function LAST_INSERT_ID().
• If we try to insert a new row with a specified value for the AUTO_INCREMENT column if the value already exists for that column, MySQL will return an error and it will not be added. If that value doesn't exist then it will be added.
• If we insert a row with a value greater than the next sequence number, it will be added, and for further rows, the value will be added incrementing 1 in this value.
• If we try to update the value of a row of the AUTO_INCREMENT column to an existing value, it will cause an error. If we update it with a value greater than the present value of the sequence, it will be updated for that row, and for further rows, the sequence number will be the same as the original sequence number and it will not be incremented to the updated value.
• If we delete the last inserted row of the table, MySQL may or may not use the deleted sequence number. It depends on the storage engine of the table. For the default MyISAM storage engine, the deleted value are not reused.

For example:

Let's create a table with an AUTO_INCREMENT column.

CREATE TABLE students(
reg_no INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(255),
stram VARCHAR(255)
);

The above query will create a table ‘students’ with three columns.

Now, insert values in this table.

 INSERT INTO students(name, stream)
 VALUES('Ashish', 'CSE'),
 	   ('Ankit', 'IT'),
 	   ('Prashant', 'CSE');

The above table will insert values in the table and the table will look like as shown below:

In the above query, the values for the column reg_no are omitted but in an AUTO_INCREMENT column, the values are auto inserted by MySQL.

Now, we delete a row using the query shown below query:

DELETE FROM students 
WHERE reg_no=3;

The new table will look like this:

Now, insert a row again.

INSERT INTO students(name, stream)
VALUES('Karan', 'IT');

Now, the updated table will look like shown below:

It can be seen that we deleted the 3rd row, but the newly inserted row has the sequence number 4 as it doesn't reuse the deleted numbers.

Now, update the value of a row.

UPDATEUPDATE students 
SET 
    name = 'Vivek',
    stram= 'CSE',
    reg_no = 7
WHERE
    reg_no = 4;

Now, the update table is:

Now, if we insert a row, the new sequence number will be the pre-existing one, not the increment in the updated value as shown below:

INSERT INTO students(name, stream)
VALUES ('Ujjwal', 'CSE');

The updated table after the above query will be:

The existing value of the sequence was 4 which was updated to 7, so the new row with value 5 is inserted.