Re: Enforce primary key on every table during dev?

Ron Johnson <ron.l.johnson@xxxxxxx> · Thu, 1 Mar 2018 14:59:25 -0600



    On 03/01/2018 02:44 PM, Daevor The
      Devoted wrote:

    
          On Thu, Mar 1, 2018 at 10:32 PM,
            David G. Johnston <david.g.johnston@xxxxxxxxx>
            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.
                    

                    David
                      J.
                    

        This is exactly my point: you cannot
          know when a Business Rule is going to change. Consider, for
          example, your Social Security number (or ID number as we call
          it in South Africa). This is unique, right?.

        
    No, the SSN is not unique. 
https://www.computerworld.com/article/2552992/it-management/not-so-unique.html

    
        Tomorrow, however, data of people from
          multiple countries gets added to your DB, and BAM! that ID
          number is suddenly no longer unique. Business Rules can and do
          change, and we do not know what may change in the future.
          Hence, it is safest to have the surrogate in place from the
          start, and avoid the potential migraine later on.
        

        Disclaimer: this is just my opinion
          based on my experience (and the pain I had to go through when
          Business Rules changed). I have not done any research or
          conducted any studies on this.
      
    
    -- 

      Angular momentum makes the world go 'round.