Primary key issue in MySql using PhPMyadmin

Question

I recently started learning some languages: html, css and now PhP and MySql. I created a sign up, log in and log out system using this tutorial:

http://www.tutorialrepublic.com/php-tutorial/php-mysql-login-system.php

I'm using XAMPP to run Apache server, MySql and PhPMyAdmin. Everything seems to work fine, except for an issue with the primary key. When my form was completed I started adding some fictional user accounts to test it out. After that I deleted them. The username and password were deleted, but the Primary Key (ID) won't change. Even though the first row should be the first ID of 1, it is stuck at 3 because the rows with ID's 1 and 2 were deleted. With this as a result: image of issue.

Can anyone help me with this?

That's because ID is set to auto_increment. meaning it will NEVER go back to 1 and 2. Each time a record is added ID = ID + 1 — Niek van der Maaden, May 11 '17 at 14:08
Go to phpmyadmin operations tab, there is AUTO_INCREMENT change it with your desired start :) — Mubashar Iqbal, May 11 '17 at 14:11
You will find the same question here: http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql — Carsten Massmann, May 11 '17 at 14:11
You can reset the [auto-increment number](https://dev.mysql.com/doc/refman/5.7/en/example-auto-increment.html), but if you're wiping all of the data, truncate works just as well. — aynber, May 11 '17 at 14:11
Possible duplicate of [How to reset AUTO_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) — Funk Forty Niner, May 11 '17 at 14:14
Possible duplicate of [How to reset AUTO\_INCREMENT in MySQL?](http://stackoverflow.com/questions/8923114/how-to-reset-auto-increment-in-mysql) — aynber, May 11 '17 at 14:14

score 0 · Answer 1 · edited May 23 '17 at 10:31

0

That's the AUTO_INCREMENT behavior. As you can see here, you can modify your auto_increment setting something like this:

ALTER TABLE foo AUTO_INCREMENT=1

But this isn't recommended.

edited May 23 '17 at 10:31

Community

1
1

answered May 11 '17 at 14:11

Michael Becerra

401
1
3
15

score 0 · Answer 2 · answered May 11 '17 at 14:11

In the table creation the id is set to auto_increment

CREATE TABLE users (
    id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
    username VARCHAR(50) NOT NULL UNIQUE,
    password VARCHAR(255) NOT NULL,
    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
);

This causes the numbers to keep incrementing even though they may not be in the database.

You can reset the auto_increment value:

ALTER TABLE `table_name` AUTO_INCREMENT=1

score 0 · Answer 3 · answered May 11 '17 at 14:13

0

You can either truncate your table (Operations tab in PHPMyAdmin) or run the following query:

ALTER TABLE `mytable` AUTO_INCREMENT = 1;

But just truncate it, it's best to just do that.

answered May 11 '17 at 14:13

Zeke

1,281
1
18
26

Primary key issue in MySql using PhPMyadmin

3 Answers3