# Jumping ID auto increment after delete
**URL:** https://discourse.cakephp.org/t/jumping-id-auto-increment-after-delete/5768
**Category:** Need Help
**Created:** [March 11, 2019, 4:47pm UTC](https://discourse.cakephp.org/t/jumping-id-auto-increment-after-delete/5768 "2019-03-11T16:47:28Z")
**Posts on this page:** 4
**Page:** 1
### Author:  [@arizzcake](https://discourse.cakephp.org/u/arizzcake)
#### Post date: [March 11, 2019, 4:47pm UTC](https://discourse.cakephp.org/t/jumping-id-auto-increment-after-delete/5768/1 "2019-03-11T16:47:28Z")
When I was delete record Users, the ID primary key auto increment always jumping, I want ID auto increment is sequence again, this sample
```auto
| ID | UserName |
| 1 | Budi |
| 2 | Joko |
| 3 | Amir |
```
when I delete users Joko, then I add new other user, the ID number is jumping
```auto
| ID | UserName |
| 1 | Budi |
| 3 | Amir |
| 4 | Faris |
```
I have some solution in stackoverflow, but does’t work,
here I have add modified file
```auto
config/app.php
```
```auto
'SQLkonek' => [
'className' => 'Cake\Database\Connection',
'driver' => 'Cake\Database\Driver\Mysql',
'persistent' => false,
'host' => 'localhost',
'username' => 'root',
'paassword' => ' ',
'database' => 'klinikucing',
'encoding' => 'utf8mb4',
'timezone' => ' ',
'cacheMetadata' => true
]
```
then I call modified above through
```auto
controller/UsersController.php
```
```auto
public function delete ($id = null)
{
$this->request->allowMethod(['post', 'delete']);
$kon = ConnectionManager::get('SQLkonek');
$user = $this->Users->get($id);
$stm = $kon->execute(array(
['SET @count = 0'],
['DELETE FROM users WHERE ID = :ID'],
['UPDATE users SET users.ID = @count:= @count + 1'],
['ALTER TABLE users AUTO_INCREMENT =1']
))
->fetchAll('assoc');
If($this->Users->$stm) {
$this->Flash->success(__('Users success delete.'));
} else {
$this->Flash->error(__('User delete failed, try again.'));
}
return $this->redirect(['action' => 'index']);
```
The error message was shown
```auto
Warning (2): PDO::prepare() expects parameter 1 to be string, array given [CORE\src\Database\Driver\Mysql.php, line 138]
Warning (512): Unable to emit headers. Headers sent in file=C:\xampp\htdocs\klinikucing\vendor\cakephp\cakephp\src\Error\Debugger.php line=853 [CORE\src\Http\ResponseEmitter.php, line 48]
Warning (2): Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\klinikucing\vendor\cakephp\cakephp\src\Error\Debugger.php:853) [CORE\src\Http\ResponseEmitter.php, line 148
Warning (2): Cannot modify header information - headers already sent by (output started at C:\xampp\htdocs\klinikucing\vendor\cakephp\cakephp\src\Error\Debugger.php:853) [CORE\src\Http\ResponseEmitter.php, line 181]
Argument 1 passed to Cake\Database\Statement\PDOStatement::__construct() must be an instance of PDOStatement or null, boolean given, called in C:\xampp\htdocs\klinikucing\vendor\cakephp\cakephp\src\Database\Driver\Mysql.php on line 139
Error in: ROOT\vendor\cakephp\cakephp\src\Database\Statement\PDOStatement.php, line 33
```
My CakePHP version is 3.7.2
I hopefull that someone can help me, thanx
---
### Author:  [@FinlayDaG33k](https://discourse.cakephp.org/u/FinlayDaG33k)
#### Post date: [March 12, 2019, 9:16am UTC](https://discourse.cakephp.org/t/jumping-id-auto-increment-after-delete/5768/2 "2019-03-12T09:16:33Z")
First of all, why don’t you use the CakePHP ORM for all of this?
Second of all, why are you fiddling with the user IDs like that?
Is there a specific reason you want to keep your user IDs in sequence?
---
### Author:  [@Zuluru](https://discourse.cakephp.org/u/Zuluru)
#### Post date: [March 12, 2019, 5:46pm UTC](https://discourse.cakephp.org/t/jumping-id-auto-increment-after-delete/5768/3 "2019-03-12T17:46:13Z")
Yeah, that’s not at all normal database usage. I understand the OCD urge to have everything sequential, but it has so many downsides that it’s really, really not worth trying to make it work.
---
### Author:  [@FinlayDaG33k](https://discourse.cakephp.org/u/FinlayDaG33k)
#### Post date: [March 13, 2019, 2:08pm UTC](https://discourse.cakephp.org/t/jumping-id-auto-increment-after-delete/5768/4 "2019-03-13T14:08:56Z")
It’ll get especially complicated once you get fiddling around with relations.