1 Reply Latest reply: Nov 25, 2012 11:56 PM by Billy~Verreynne RSS

    Having more columns as composite Primary Key

    Gokul Babu
      Hi ,

      Is it advisable to have a composite primary key(using around 8 columns , each around 10 characters length)? if yes, Will it cause any performance issue due to indexing?

      Even if we consider the option of having a surrogate key as PK and unique constraint defined over the combination of 8 columns, still it will affect the performance in the same way right?

      Any advantage of one over the other? Please advice

        • 1. Re: Having more columns as composite Primary Key
          Not the correct forum for this question - you should rather ask this in {forum:id=61}.

          The issue you have raised, have been discussed numerous times in the general questions database forum - some view surrogate keys as a better option, others prefer natural keys. Performance wise, hitting a B+tree is hitting a B+tree. You are unlikely to see any real performance differences between hitting a single value B+tree and hitting a multi-value B+tree.

          If you do have a surrogate key, what do you do to enforce the natural PK? Use a unique index? In which case, what are the costs of now having to have the table manage (update/delete/insert) 2 sets of indexes for surrogate PK and natural PK?

          There are a number of issues to consider.