Say I have a table with 4 rows and the id column (primary key) has the values 1,2,3,4.
When I delete row 2, is it possible in Base to update the primary keys so that the remaining 3 rows get the id values of 1,2,3 and not 1,3,4?
I hope someone answers this. I’d like to know also.
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:
- It takes time
- (more importantly) the database doesn’t necessarily know how to update all uses of that primary key id across the database
- 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.