Primary Key values should always be arbitrary.
No, they should not. In an ideal world, the Primary Key is the unique identifier that inherently denotes the current record. Using arbitrary values is a kludge by people who simply do not understand how a database works.
the primary key values themselves should never be visible outside of the database: if the business needs unique identifiers, you should coin your own, e.g. with UUIDs. Never attempt to re-use PK values, or for that matter, business-known identifiers. In keeping with the principles of database normal-forms, all keys should be nothing other than abstract identifiers, carrying no embedded information.
That is ridiculous. There is nothing wrong with reusing PK values. And it has nothing to do with normal forms.
| [reply] |
Hello, I would strongly disagree with the last statement, that is is OK to reuse value of a primary key. Such approach invalidates any attempt to compare previous state and current state of a record in a table (since meaning of the id value is changed). Of course, this sentiment is probably irrelevant to the problem you are trying to solve...
| [reply] |
Such approach invalidates any attempt to compare previous state and current state of a record in a table
If a previous value has meaning, it should never have been deleted. Also, in most schemes, PK re-use come some time after its original use.
| [reply] |
Thanks, I will see if it works to put the defrag code in the model section, although it will be triggered by an insert originating in view and passing thru controller.
| [reply] |
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
| [reply]
[d/l]
[select] |