Possible to update primary key on delete in Base?

answered 2013-03-06 10:50:42 +0200

qubit
5816 ● 3 ● 50 ● 43

Hi Anon, @DonLow,

In most databases, primary key values are not shuffled around upon deletion of rows. There are a couple of reasons for doing this:

1. It takes time
2. (more importantly) the database doesn't necessarily know how to update all uses of that primary key id across the database
3. The primary key might even be used/referenced outside of the database

What's the use case in which you'd want to renumber all of the primary keys?

If you really need to have that kind of id in your table, instead of changing all of the primary keys, why not just put a new column "order_id" into your table. Whenever you delete a row from the table, iterate over the remaining rows (in ascending order by primary key) and set order_id to the appropriate value.