0

I have a table in my MySQL database where the id's of the entries starts from 3000 because the previous rows were deleted. What I would like achieve is to change the value of the id columns. I would need the rows to start from 1. If I have 1900 entries, I would need the id's from 1 to 1900. The id column in my database is set to AUTO_INCREMENT and the id is primary key.

My question is that is it possible to achieve this with an SQL command? Or I should rather create a new database and transfer the objects with new id's?

rihekopo
  • 3,241
  • 4
  • 34
  • 63
  • 4
    My question is "why?" – Caius Jard Sep 07 '19 at 18:53
  • 2
    The first question is: Why do you care? It's a really bad idea to change primary keys. If the table is referenced by any other table, then you will have referential integrity issues. – daShier Sep 07 '19 at 18:53
  • In a nutshell, the id's are used in url's and must start from 1. @CaiusJard Jard – rihekopo Sep 07 '19 at 18:57
  • Why must they start from 1? – Schwern Sep 07 '19 at 19:04
  • But what if another few records are deleted later? You should not be relying on contiguous values for the keys. However, if you must, and if the table is referenced by other tables, then your best bet is to `SELECT` the records into a new table, temporarily drop your foreign key constraints, then replace the references to the `ID` from the old table with the new `ID` (you will need to match up your records), then create the new foreign key relationship and finally drop the old table. _Is it worth all that?_ – daShier Sep 07 '19 at 19:06
  • 1
    Not wishing to sound harsh but "Because they go in a url" might be among the weakest of reasons I've heard for having contiguous IDs that start from 1 - sounds like security breach waiting to happen., – Caius Jard Sep 07 '19 at 19:17
  • If you need explicit numbering, I would suggest adding a new column just for that. – Schwern Sep 07 '19 at 19:42

2 Answers2

4

As mentioned here https://stackoverflow.com/a/43137256/5031885

UPDATE table_name SET id  = 0 - id;
set @counter = 0;
UPDATE table_name SET id  = (@counter := @counter + 1);

This will first set all id values to negative equivalents and then will loop each row, update and increment the counter.

This solution will not work if id column is unsigned. Also, keep in mind that this will not change the current AUTO_INCREMENT pointer.

To help with auto-increment you can use:

ALTER TABLE table_name AUTO_INCREMENT = 1;

Even if we assign 1 to AUTO_INCREMENT, it will continue with the next key as it should.

Pavel Cechir
  • 111
  • 1
  • 7
0

You should better drop the column and again add it as it will surely lead to inconsistancy of data as its a primary key plus if it becomes a foreign key as a reference in other table then it would create more problems even after dropping the column

You need to drop the column and then add it again with autoincrement and if it is also a foreign key then you have to drop the constraints from both the tables using CASCADE.

Himanshu
  • 3,830
  • 2
  • 10
  • 29
  • Only after _all_ records have been deleted. If you do this when there are still records with id 3000+, then the next id will be 1 greater than the last record in the table. It will not renumber the records. – Bill Karwin Sep 07 '19 at 19:56
  • Did you mean 301 would be the value starting with. Yeah i guess i did it wrong umm.. will update i guess better option is to drop this column and again recreate it – Himanshu Sep 08 '19 at 15:18
  • No — the better option is to forget about renumbering primary key values. – Bill Karwin Sep 08 '19 at 16:15