0

I have a problem with my MySQL Database. I searched for a long time, but there was nothing, which could help me. I have a Database with three columns: id, username and password. For example there are 3 lines in the database.

1, test1, testpw1
2, test2, testpw2
3, test3, testpw3

When I delete line 2, how can I change the id (auto_increment) in the whole database?

Now it would be like this:

1, test1, testpw1

3, test3, testpw3

I want it to be like this:

1, test1, testpw1

2, test3, testpw3

Thanks a lot Jannes

M Khalid Junaid
  • 63,861
  • 10
  • 90
  • 118
Jannes
  • 19
  • 5
  • 7
    DON'T - It shouldn't ever be necessary, PK values should be unique, not used for order or maintaining sequence; and changing your autoincrement PK values can have adverse affects on related tables – Mark Baker Aug 22 '14 at 18:35
  • That is not how auto_increment works. I you will change the id`s you have to change it for every record manually. – Jens Aug 22 '14 at 18:35
  • AUTO_INCREMENT has *no* meaningful numeric value (except perhaps some semblance of an ordering guarantee that is usually valid). ["Compacting" an AUTO_INCREMENT column can be done](http://stackoverflow.com/questions/22488650/how-to-update-the-id-column-starting-from-1-again/22489808#22489808), but it often indicates misunderstanding of such a surrogate key and I do not recommend it. – user2864740 Aug 22 '14 at 18:36
  • Mark is right; adjusting the primary key values is unlikely to be the best approach. If you will explain why you want the gaps removed, we may be able to provide a useful alternative. – George Cummins Aug 22 '14 at 18:39
  • Would it be possible to "reset" the auto_increment column, so every line gets a new id? – Jannes Aug 22 '14 at 19:23
  • It's possible, but it should never be necessary (what is your reason for needing to do this), and it can cause serious problems in any related tables – Mark Baker Aug 22 '14 at 19:38

1 Answers1

0

What you do for that, is you create another column called "deleteFlag" and set it up as either a 1 or a 0 data value, and when your displaying data, make sure you include deleteFlag set as a 1....

When you delete a row, you set deleteFlag to a 0. Thus when displaying the data, your code should be set to show only deleteFlags with 1.

Paulie
  • 6,535
  • 4
  • 20
  • 35
  • Actually that is not what I want. Isn't there any way to do it like I said above? Maybe not with AUTO_INCREMENT... – Jannes Aug 22 '14 at 19:19