Merve Kaya Merve Kaya - 5 months ago 26
MySQL Question

SQL userID auto increment

Mysql database is like that;

userID -> primaryKey, required, unsigned integer, auto increment, unique
usermail -> unique,varchar,required


I'm creating new users. userId is auto increment. If usermail inserted before,it occurs an error. My question is this

Let's think that userID is between 1-9.(there are 9 users now).
Somebody has inserted same usermail before and took error.
After that,new user inserted another usermail but userID became 11 instead 10.
Why? What should i do to make it 10?

Answer

Is it necessary for you to increment the userID from Database properties itself. Because what I am saying is, before inserting any records, just retrieve the last userID from database, store it in a integer variable and then increment it and insert back into database along with the new record.

Say your last user id was 9. Use this query - Select max(userID) from "your table";

int x=0;
SqlDataReader dr=com.ExecuteReader();
if(dr.HasRows)
{
    While(dr.Read())
    {
       x=Convert.ToInt32(dr.GetString(0).ToString);
    }
}
x=x+1;

Now since you have new x value, easily insert it back to database with new record. So that auto increment will be followed and even though failure occurs insertion will not be done and UserID does not increase.