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.l.johnson@cox.net 
> <mailto:ron.l.johnson@cox.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.