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