Re: Enforce primary key on every table during dev?

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



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

    
          On Thu, Mar 1, 2018 at 9:00 PM, Ron
            Johnson <ron.l.johnson@xxxxxxx>
            wrote:

            
                  On
                    03/01/2018 12:32 PM, Daevor The Devoted wrote:

                  
                      On Thu,
                          Mar 1, 2018 at 8:18 PM, Ron Johnson <ron.l.johnson@xxxxxxx>
                          wrote:

                        
                                On
                                  03/01/2018 11:47 AM, Daevor The
                                  Devoted wrote:

                                
                                    On
                                        Thu, Mar 1, 2018 at 2:07 PM,
                                        Rakesh Kumar <rakeshkumar464@xxxxxxx>
                                        wrote:

                                      
                                              >Adding a surrogate key
                                              to such a table just adds
                                              overhead, although that
                                              could be useful

                                              >in case specific rows
                                              need updating or deleting
                                              without also modifying the
                                              other rows with

                                              >that same data -
                                              normally, only insertions
                                              and selections happen on
                                              such tables though,

                                              >and updates or deletes
                                              are absolutely forbidden -
                                              corrections happen by
                                              inserting rows with

                                              >an opposite
                                              transaction.

                                              
                                            I routinely add
                                            surrogate keys like serial
                                            col to a table already
                                            having a nice candidate keys

                                            to make it easy to join
                                            tables.  SQL starts looking
                                            ungainly when you have a 3
                                            col primary

                                            key and need to join it with
                                            child tables.

                                            
                                    I was
                                      always of the opinion that a
                                      mandatory surrogate key (as you
                                      describe) is good practice.
                                    Sure there
                                      may be a unique key according to
                                      business logic (which may be
                                      consist of those "ungainly"
                                      multiple columns), but guess what,
                                      business logic changes, and then
                                      you're screwed! 
                                  
                              
                              And so you drop the existing index and
                              build a new one.  I've done it before, and
                              I'll do it again.

                                
                                    So using a
                                      primary key whose sole purpose is
                                      to be a primary key makes perfect
                                      sense to me.
                                  
                                
                               I can't stand synthetic keys.  By
                              their very nature, they're so
                              purposelessly arbitrary, and allow you to
                              insert garbage into the table.

                              
                      Could you perhaps
                        elaborate on how a surrogate key allows one to
                        insert garbage into the table? I'm afraid I
                        don't quite get what you're saying.
                    
                
                If your only unique index is a synthetic key, then you
                can insert the same "business data" multiple times with
                different synthetic keys.

                  
                  -- 

                    Angular momentum makes the world go 'round.
                
            
        That might be where we're talking past
          each other: I do not advocate for the arbitrary primary key
          being the only unique index. Absolutely not. Whatever the
          business rules say is unique must also have unique indexes. If
          it's a business constraint on the data, it must be enforced in
          the DB (at least, that's how I try to do things).
      
    
    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.

    
    -- 

      Angular momentum makes the world go 'round.