1

I've a table in MySql which includes user_id as First field. This field is a primary key and it is defined as Auto Incrementing. The problem is whenever I delete a row from the table by using the command,

delete from user where username="username"; //Username is the Second field

It gets deleted, but When I created a new Entry, the user_id is not adjusted by overwriting the deleted row. Suppose I deleted the 3rd row in table which is the last row, then my new entry is created as user_id = 4 even the user_id = 3 doesn't exist now.

Is this how the Sqlworks works or should I have make changes in my query?

Brian Tompsett - 汤莱恩
  • 5,753
  • 72
  • 57
  • 129
user562
  • 88
  • 1
  • 11
  • 1
    Its because the user_id is auto-incremented primary key. – Abhik Chakraborty Sep 09 '14 at 07:24
  • 1
    This is by design. Auto_increment ids are designed to be only used once. – MrTux Sep 09 '14 at 07:25
  • 1
    I think you need to do some research and some reading on how SQL works – Divi Sep 09 '14 at 07:26
  • 1
    This is the expected behaviour of the database on auto-incremented keys. If you want to reset the counter to some value you can check here: http://stackoverflow.com/a/8923132/99557 – mpcabd Sep 09 '14 at 07:27
  • yeah, maybe. Anyway thank for the clarifiacation guys. Have to do my further learning on how it works. – user562 Sep 09 '14 at 07:27
  • @mpcabd , that will very much use.. Thank you – user562 Sep 09 '14 at 07:29
  • You can check this link to see how it works: http://stackoverflow.com/questions/4562479/how-does-mysql-auto-increment-work . – Sal00m Sep 09 '14 at 07:30
  • 1
    If you want change auto increment start Id alter table user AUTO_INCREMENT=3; But it raises some problem . The User Information mapped with another table means, The information will be conflict with another user. – Punitha Subramani Sep 09 '14 at 07:34

1 Answers1

1

I recommend you to create another field so you have

id | user_id | username

id should be auto_increment

To insert you could count entrys in DB and raise the number +1.

Then you would have the result you want.

Of curse you could use id for this but in this case you allways have to reindex you user_id.

And actually thats not a way a primary key should be used.

Example:

Lets say you have a company with 100 customers. Today Customer 50 has customernumber 50 and tomorrow he has 48 because 2 other customers left your company. Makes no sence :D

Dwza
  • 6,494
  • 6
  • 41
  • 73
  • Yes, That will become a conflict. My program is login and Registration which uses many tables and If I change my auto increment value, it'll become a whole mess. – user562 Sep 09 '14 at 07:41