How to re-arrange database primary key

Question

Good day I create database at localhost for website. and put some info, than i delete and re-enter info from database. and now for 'id' primary key i have more than 200 rows. I want to re-arrange primary key.

for example

id |name
1  |Samuel
2  |Smith
4  |Gorge
15 |Adam
19 |David

i want to have

id |name
1  |Samuel
2  |Smith
3  |Gorge
4  |Adam
5  |David

Is it possible to do with any command?

Answer 1

Consider this scenario: Gorge sends some offensive emails, and people complain and his account (#4) is denylisted.

Then you reorder your primary key values, and Adam is now assigned id 4. Suddenly, he finds himself banned! And lots of people mistrust him without cause.

Primary keys are not required to be consecutive -- they're only required to be unique. It's normal for there to be gaps, if you sometimes ROLLBACK transactions, or DELETE rows.

Answer 2

The purpose of a primary key is to uniquely identify each row, so rows in one table can be related to rows in another table. Remember, this is a relational database and part of the meaning of "relational" is that entities are related to each other.

In other words, you don't want to change the primary key of rows, because that will break links from other tables. MySQL does not guarantee that auto incremented values are inserted without holes. In fact, as you have discovered, deletions and re-inserts cause problems.

Your interpretation of the "primary key" as a sequential number with no gaps assigned to each row maintained by the database is simply not correct.

Even though you don't want to do this, you can. I advise against it, but you can:

declare @rn := 0;

update t 
    set id = (@rn := @rn + 1)
    order by id;

If you want to enforce this over time, you will need to learn about triggers.

Answer 3

Most likely the primary key is being auto generated from some sort of auto increment sequence. In that case you can take the following steps:

1) update all the primary keys to the next value of the sequence: this will collapse all of the values into a contiguous range. In your case those ids will be 20, 21, 22, 23, 24. Postgres example:

UPDATE my_table SET id = nextval(my_table_id_sequence)

2) reset the sequence to start at 1: In Postgres this would look like the following:

ALTER SEQUENCE my_table_id_sequence RESTART WITH 1

3) update the values to the next value of the sequence again: Now can move all the rows back "down" to start at 1, and in your case they will be 1, 2, 3, 4, 5. It is important to first consolidate all the values at the "top" of the sequence before resetting, because that way we guarantee that there wont be any primary key collisions at the "bottom"

UPDATE my_table SET id = nextval(my_table_id_sequence)

NOTE: this approach only works if there are no foriegn keys which are referring to the primary key of the table. If there are foreign keys you can still take the same approach, but first do these 3 steps:

1) find all of the related tables/columns that are referencing this primary key column

2) create a function that will cascade updates to the pk out to all fks

3) create a trigger that will execute the above function whenever the pk is updated: at this point, when we update the primary key column, all of the related foreign keys will also be updated. Depending on the database, you might need to explicitly defer constraint validation, or do the whole thing in one transaction.

For an example of what the above might look like in Postgres you can take a look at my answer here How Do I Deep Copy a Set of Data, and Change FK References to Point to All the Copies?

Answer 4

You could drop the primary key column and re-create it. All the ids will then be reassigned, I assume in the order in which the rows were inserted.

  alter table your_table drop column id; 

then to create it

ALTER TABLE  `your_table_name` ADD  `id` INT NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;