Re: Enforce primary key on every table during dev?

On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net> wrote:

> Why have the overhead of a second unique index? If it's "ease of joins",
> then I agree with Francisco Olarte and use the business logic keys in your
> joins even though it's a bit of extra work.
>

​The strongest case, for me, when a surrogate key is highly desirable is
when there is no truly natural key and the best key for the model is
potentially alterable. Specific, the "name" of something. If I add myself
to a database and make name unique, so David Johnston, then someone else
comes along with the same name and now I want to add the new person as, say
David A. Johnston AND rename my existing record to David G. Johnston. I
keep the needed uniqueness ​and don't need to cobble together other data
elements. Or, if I were to use email address as the key the same physical
entity can now change their address without me having to cascade update all
FK instances too. Avoiding the FK cascade when enforcing a non-ideal PK is
a major good reason to assign a surrogate.

David J.