1

I'm using a SQlite database and Django's QuerySet API to access this database. I wrote data sequentially into the database and each entry has a simple ID as the primary key (this is the Django default). This means that there is a continuous sequence of IDs now in the database (entry 1 has ID 1, entry 2 has ID 2, and so on). Now I needed to delete some entries again. This means that the sequence of IDs is discontinuous now (entry 1 has ID 1, but entry 2 might have ID 3, 8, 1432 or anything else, but not 2).

How can I restore this continuous sequence of IDs again and associate them with the remaining entries in the database? Is there a way to do this with Django's QuerySet API or do I need to use plain SQL? I have no experience with plain SQL, so some working code would be very helpful in this case. Thank you!

pemistahl
  • 9,304
  • 8
  • 45
  • 75
  • 8
    in general, you DO NOT want to do this. leave the gaps where they are and continue from the largest number. – Randy Oct 30 '12 at 19:40
  • 1
    There is no benefit to filling the gaps, just ignore them and take the next value like Randy states. – Limey Oct 30 '12 at 19:44
  • 1
    @Randy Can you tell me in a bit more detail why you don't recommend this? – pemistahl Oct 30 '12 at 19:52
  • 3
    What would be the goal of filling the gaps? You are setting yourself up for a major headache, because you probably will reference some of these IDs in your application. Worse yet, the existing IDs might have been exported to third-party systems, or lie around in Excel-sheets. From a technical perspective, having an unbroken sequence might seem like the right thing, but really, in the long run you will regret it. Or, simplified: `users, eh?`. – SchmitzIT Oct 30 '12 at 20:06
  • 3
    Such ids are, more often than not, used in references elsewhere (other tables, as foreign keys, or in other systems). If you were to re-use them, you'd create confusion in your system. Don't fill in the gaps, just move on and ignore them. The ID is just an opaque value anyway, something you shouldn't otherwise care about. – Martijn Pieters Oct 30 '12 at 20:15
  • 1
    Just to add to what everyone else is saying, many database engines *themselves* will not make guarantees that all your IDs will be sequential. For example: start a transaction, insert a new row, then rollback the transaction without committing. In many systems, you will now have a gap in the IDs. **Don't worry about it!!** – Francis Avila Oct 30 '12 at 20:26

1 Answers1

4

I cannot think of any situation in which doing this would be desirable. The best primary keys are immutable (although that's not a technical requirement) and the very purpose of using non-meaningful integer primary keys is to avoid having to update them.

I would even go so far as to say that if you require a meaningful, unbroken sequence of integers, create a separate column in your table, keep the primary key with its sequence breaks, and renumber the new "sequence" column when needed.

However, you may have requirements that I can't think of. If you really need to change the values in those keys make sure that all the references to that column in your database are protected by FOREIGN KEY constraints and check out the ON UPDATE CASCADE option when you declare a foreign key. It will instruct the database to do the updating for you.

But if you don't have to this, don't.

Larry Lustig
  • 49,320
  • 14
  • 110
  • 160
  • Thank you Larry, then I will keep away of doing this. It's a bit complicated to explain why I would need to renumber the database entries, but I think I can reach my goal in another way as well. But your answer was helpful anyway: Now I know that I shouldn't do it. – pemistahl Oct 30 '12 at 20:48