Re: Enforce primary key on every table during dev?

Ron Johnson <ron.l.johnson@xxxxxxx> · Thu, 1 Mar 2018 15:03:33 -0600



    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@xxxxxxx> 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.