Re: Enforce primary key on every table during dev?

On 03/01/2018 02:32 PM, David G. Johnston wrote:
> On Thu, Mar 1, 2018 at 1:24 PM, Ron Johnson <ron(dot)l(dot)johnson(at)cox(dot)net
> <mailto: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.

There's always the "account number", which is usually synthetic. Credit Card
numbers are also synthetic.  ICD numbers are (relatively) synthetic, too.

But that doesn't mean we have to use them willy-nilly everywhere.

--
Angular momentum makes the world go 'round.