Data Structure
Networking
RDBMS
Operating System
Java
MS Excel
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
Reset the primary key to 1 after deleting all the data in MySQL?
To reset the primary key to 1 after deleting the data, use the following syntax
alter table yourTableName AUTO_INCREMENT=1; truncate table yourTableName;
After doing the above two steps, you will get the primary key beginning from 1.
To understand the above concept, let us create a table. The query to create a table is as follows
mysql> create table resettingPrimaryKeyDemo -> ( -> UserId int NOT NULL AUTO_INCREMENT PRIMARY KEY -> ); Query OK, 0 rows affected (0.66 sec)
Insert some records in the table using insert command. The query is as follows −
mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.15 sec) mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.10 sec) mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.08 sec) mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.12 sec)
Display all records from the table using select statement. The query is as follows −
mysql> select *from resettingPrimaryKeyDemo;
The following is the output
+--------+ | UserId | +--------+ | 1 | | 2 | | 3 | | 4 | +--------+ 4 rows in set (0.00 sec)
Here is the query to reset the primary key to 1
mysql> alter table resettingPrimaryKeyDemo AUTO_INCREMENT=1; Query OK, 0 rows affected (0.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> truncate table resettingPrimaryKeyDemo; Query OK, 0 rows affected (0.89 sec)
Check the records from the table. The query is as follows −
mysql> select *from resettingPrimaryKeyDemo; Empty set (0.00 sec)
Insert some records from the table using insert command. The query is as follows −
mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.12 sec) mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.10 sec) mysql> insert into resettingPrimaryKeyDemo values(); Query OK, 1 row affected (0.10 sec)
Now check the table primary key beginning from 1. The query is as follows −
mysql> select *from resettingPrimaryKeyDemo;
The following is the output
+--------+ | UserId | +--------+ | 1 | | 2 | | 3 | +--------+ 3 rows in set (0.00 sec)
7K+ Views
