0

My sql data base includes index numbers with lots of gaps between 

id = 1, 2, 3, 9, 10, 25, ...

because some data sets together with the corresponding index have been deleted. I would like to rebuild this automatic incrementing index in a way, that index numbers with no gaps are shown in the data base.

id = 1, 2, 3, 4, 5, 6, ...

What would the correct sql statement look like?

I have tried something like 

ALTER TABLE table1 . id REBUILD

but this includes syntax errors.

Werner Lechner
  • 11
  • 2
  • you can try to drop primary key and then add it back like this `alter table table-name add column `id` int(10) unsigned primary KEY AUTO_INCREMENT;` – Viktar Pryshchepa Apr 13 '19 at 10:58

2 Answers2

0

You can do this thru designer. Go to Table-->Index then right click on index and select Rebuild.

Similar question, Script for rebuilding and reindexing the fragmented index?

Rima
  • 1,447
  • 1
  • 6
  • 12
0

NO, NO, NO

Don't ever do this. If you think you'll have to do it, you have a wrong approach.

ID stands for "identifier" and it sole purpose is to UNIQUELY identify the associated data.

Create a second column "sequence" for this, which is under YOUR control and let the database have THEIR way to handle data.

Honk der Hase
  • 2,459
  • 1
  • 14
  • 26
  • Thank you for the hints, however there are no simple sql expressions with a clear syntax to rebuild an index. Therefore I deleted the index column in my data base and added a new index column with the same name. This worked perfect. The data lines were automatic numbered in a series from 1,2, 3, ..., N. Of course, first I saved the data base with a new name by the copy command. – Werner Lechner Apr 14 '19 at 20:21
  • You're still abuisng the id field for another purpose... why are you using autoincrement then anyway? – Honk der Hase Apr 14 '19 at 20:24