This is how SQL works actually. Had the same doubt when I first came across the same situation but then realised I wasn't wrong this is just how SQL works. Google about this and you will explore more, the reason and why it's still there :)
Rolling back a transaction will still increment the primary key
I am using Laravel as an API for a ios app.
Now I have setup a DB transaction so that two tables gets created when a user makes an account, one table containing basic info email, password etc, and another table containing some more private info address etc
But I noticed that if I fail the transaction and toggle a rollback() the primary key in the users table will still remain incremented after the rollback. Worth saying is that the foreign key in the user_info table is linked to the primary key in the users table.
Is there any way to also rollback the increment in the table?
My code for testing:
DB::beginTransaction();
try
{
$newUser = User::create([
'user_type' => $request->input('user_type'),
'username' => $request->input('username'),
'email' => $request->input('email'),
'password' => $request->input('password')
]);
}
catch (\Exception $e)
{
DB::rollback();
return response()->json(['errors' => $e->getMessage()],400);
}
try
{
$newUserInfo = UserInfo::create( ['u_info_id' => $newUser->user_id, 'u_state_id' => '2', 'u_city_id' => '7'] );
}
catch (\Exception $e)
{
DB::rollback();
return response()->json(['errors' => $e->getMessage()],400);
}
DB::commit();
Please or to participate in this conversation.