I believe that you have somehow entirely missed my most-essential(!) point that “defrag code” is not only unnecessary, but specifically should not be pursued.

If you suppose that Primary Keys should need to be “defragmented,” then this indicates that you are attaching to them “some (any ...) intrinsic meaning.”   (Why?   Because it seems to bother you that the values might not be consecutive!)

If, for any legitimate business reason or otherwise, you need some sort of identifier that is “consecutive” and that therefore might from time-to-time need to be “defragmented” ... (although I certainly do not recommend such a thing) ... “feel free.™”   However, do not make this value “your primary key!”   Instead, make it “just another column.”

(For a more thorough background of what I am talking about, let me please now refer to Database Normalization in WikiPedia.)

The “primary key” of any and every(!) table within your database should be a perfectly-arbitrary, yet guaranteed-unique, “identifier ... and nothing more™ ...” of a particular row.   All such values should be entirely internal to the database, and never exposed to, therefore also never of material interest to, the outside world.

If you feel any need whatsoever to “defragment” those values, then this necessarily proves(!) that you are violating this precept ... and you should immediately redesign your database architecture accordingly.

“Do you really care what the exact numeric value of ‘your credit-card number’ is?”   “No, as long as it is unique!”   (And, but of course, as long as it works.)   Otherwise, it’s just a well-behaved “primary key.”   It uniquely identifies your account, but does nothing more.   And the credit-card company doesn’t give a damn whether it is “consecutive.”   Any valid but otherwise-random string of numbers is equally satisfactory ... as any good primary-key should always be.

The “primary key” of any and every(!) table within your database should be a perfectly-arbitrary, yet guaranteed-unique, “identifier ... and nothing more™ ...” of a particular row. All such values should be entirely internal to the database, and never exposed to, therefore also never of material interest to, the outside world.

This touches on the general question on whether or not to use Natural Keys [1]. Apparently you think one should not use natural keys. You think surrogate keys are always necessary.

That's fine, but it certainly isn't received wisdom. It is an open question: many people will argue that natural keys have important advantages [2]. I will always use natural keys where possible.



[1] https://en.wikipedia.org/wiki/Natural_key

[2] http://www.databasesoup.com/2015/03/primary-keyvil-reprised.html

Nice post. That covers it at a high level pretty well. Thank you.

it certainly isn't received wisdom.

Not only that, i think most people who do that, do so out of sheer ignorance. When i ask them for the reasons, they repeat non-trueisms about how the RDBMS does things.