AUTO_INCREMENT with Create Statement

In this, we will look to Create a table and with it, we will mention the “AUTO_INCREMENT” As discussed it will manage the id by itself.

Syntax:

CREATE TABLE table_name (

id INT AUTO_INCREMENT PRIMARY KEY,

–Other columns

);

Example

create table AutoIncrement(
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(50),
    age INT
);

Now insert some values into it to see whether the id is generating by itself

insert into AutoIncrement(name, age) 
values  ("John",20),
            ("Mark",35),
            ("Johnson",40);

Output:

So we have not given the values for the id column but as we have set the column to AUTO_INCREMENT it will automatically assign the value according to the last value used for the table.

Now we will delete the row with id=3 to see if the table is recalculating the ID:

delete from AutoIncrement where id=3;
select * from AutoIncrement  

Output:

Now add another row by using the below query and apply the select statement.

insert into AutoIncrement(name, age) 
values  ("Watson",20);

select * from AutoIncrement  

Output:

So it is clear from the above query that AUTO_INCREMENT doesn’t take the last ID from the database but it will take the ID based on the last inserted ID.

We can also change the AUTO_INCREMENT starting value other than 1 by using giving AUTO_INCREMENT = value property.

In all tables, we always have a primary key which is used to identify rows uniquely. For that, we generally use integers as a type of column. When adding each row, we assign a new integer to it. Whenever we insert a new row, we don’t want to manually get the last integer and use it in the INSERT query. Instead, we want an option that can automatically generate a new ID every time we try to insert it.