How to Create id with AUTO_INCREMENT in MySQL?
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.
Solutions for AUTO_INCREMENT IDs in MySQL
So we have some common solutions with some built-in functions and some with procedures(Triggers) that we will create. Let’s first understand them.
- AUTO_INCREMENT: This is the built-in property that is used to solve the above issue. It will add the new ID after the last one automatically and we don’t have to give anything related to the primary key in the Insert statement.
- Trigger: This approach will require the understanding of triggers. It will trigger the procedure as soon as the insert query is run. We will create a Trigger which will run before actual insertion in the database so that we can count the id and then we can append the id by ourselves.
1. 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:
id |
name |
age |
---|---|---|
1 |
John |
20 |
2 |
Mark |
35 |
3 |
Johnson |
40 |
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:
id |
name |
age |
---|---|---|
1 |
John |
20 |
2 |
Mark |
35 |
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:
id |
name |
age |
---|---|---|
1 |
John |
20 |
2 |
Mark |
35 |
4 |
Watson |
20 |
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.
2. AUTO_INCREMENT with Alter Statement
Now let’s say we have a table that was created without AUTO_INCREMENT
. We can use the ALTER
statement to add it.
Let’s Create a table without AUTO_INCREMENT
create table AutoIncrementAlt(
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
Insert the Rows into it
insert into AutoIncrementAlt(id,name, age)
values (1,"Watson",20),
(2,"Mark",35),
(4,"Johnson",40);
select * from AutoIncrementAlt
Output:
id |
name |
age |
---|---|---|
1 |
Watson |
20 |
2 |
Mark |
35 |
4 |
Johnson |
40 |
Now we will add AUTO_INCREMENT to this and remember if we give only AUTO_INCREMENT then it will start from 1 but here we want it to start from 5
ALTER TABLE AutoIncrementAlt
MODIFY COLUMN id INT AUTO_INCREMENT;
ALTER TABLE AutoIncrementAlt AUTO_INCREMENT = 5;
And now we can add rows
insert into AutoIncrementAlt(name, age)
values ("JOHN",20);
select * from AutoIncrementAlt;
Output:
id |
name |
age |
---|---|---|
1 |
Watson |
20 |
2 |
Mark |
35 |
4 |
Johnson |
40 |
5 |
John |
20 |
By this, we can use AUTO_INCREMENT to achieve the auto-incrementing primary key.
3. Create AUTO_INCREMENT Id Without Trigger
so now we won’t Auto_Increment with column. But we will mimic this behavior with triggers. The trigger will automatically be called when an insert query is applied.
First, let’s create another table for better understanding.
create table AutoIncrementWithTrigger(
id INT PRIMARY KEY,
name VARCHAR(50),
age INT
);
Now we will create a trigger which will be called Before Insert.
DELIMITER //
CREATE TRIGGER trg_before_insert
BEFORE INSERT ON AutoIncrementWithTrigger
FOR EACH ROW
BEGIN
DECLARE last_id INT;
SET last_id = (SELECT COALESCE(MAX(id), 0) FROM AutoIncrementWithTrigger);
SET NEW.id = last_id + 1;
END;
//
DELIMITER ;
Let’s See what this trigger is doing.
- BEFORE INSERT – Mentions when the trigger should execute
- FOR EACH ROW – Tells to execute the trigger for all rows and not for the whole block
- COALESCE(MAX(id), 0) – It will find the maximum id used in the table and if id is not assigned then it will give 0.
- NEW.id = last_id+1 – This will assign the incremented id to the newly added row.
Now we will insert the rows to look at the results.
insert into AutoIncrementWithTrigger(name, age)
values ("John",20),
("Mark",35),
("Johnson",40);
Output:
id |
name |
age |
---|---|---|
1 |
John |
20 |
2 |
Mark |
35 |
3 |
Johnson |
40 |
By this, we can use AUTO_INCREMENT to achieve the auto-incrementing primary key. Thus we can use triggers to calculate the id and assign it to new records.
Conclusion
So, Using this approach we can create a column with auto increment. This helps when we want to create a record from our backend API or service. At that time we reduce our Database calls because we don’t have to worry much about the last id. Also, it will remove the chances of any mismatch with the already present ID.
Contact Us