Ask Your Question
1

Possible to update primary key on delete in Base? [closed]

asked 2012-07-20 14:57:40 +0200

anonymous user

Anonymous

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?

edit retag flag offensive reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp
close date 2015-10-26 12:31:47.533383

Comments

I hope someone answers this. I'd like to know also.

DonLow gravatar imageDonLow ( 2012-08-14 04:00:55 +0200 )edit

1 Answer

Sort by » oldest newest most voted
0

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

qubit gravatar image

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.

edit flag offensive delete link more

Question Tools

Stats

Asked: 2012-07-20 14:57:40 +0200

Seen: 240 times

Last updated: Mar 06 '13