Tuesday, June 17, 2014

What is the difference between Primary key and Unique index in DB2

Primary key uniquely identifies  the row of a table. We declare the primary key on one(or more) column of a table. Defining the primary key we ensure, that column will not have any duplicate or NULL values. It is not a mandatory feature to be added to every table, having one is good. We define the primary key while creating the table or altering the table like below.
CREATE TABLE XMT00.CONTACT_TABL
   (CNTCT_ID             TIMESTAMP NOT NULL ,
    DATA_EFF_DT          DATE NOT NULL ,
    DATA_XPIR_DT         DATE WITH DEFAULT NULL ,
    NM_TP_CD             CHAR(1) FOR SBCS DATA NOT

    ENDG_TRN_ID          TIMESTAMP WITH DEFAULT NULL ,
    CONSTRAINT CNTCT_ID PRIMARY KEY (CNTCT_ID,START_TRN_ID))
  IN AMPXXDB.AMPAXXTS
Or like below in the ALTER table command

ALTER TABLE AXX00.ACCUM_INV_KEY
      PRIMARY KEY (ACCUM_INV_KEY_ID)
 
The uniqueness feature of primary key is achieved through the use of UNIQUE INDEX on its primary columns.We can say, Primary key, under the cover  will use UNIQUE or NON-UNIQUE index. If INDEX key allows NULLS, then we need to use WHERE NOT NULL clause to ensure non-null values are unique.
If we do not create unique index explicitly, then DB2 will create one index. But we wont be able to alter the automatically created index(like if we want to alter the features likes PCTFREE,Clustering etc).So it is always if we can create unique index and then build the primary key on that.

Technically, when we create a table with primary key, we must create a UNIQUE index on the Primary key. A very important point to remember " DB2 can not process that table on which primary key has been defined  but no indexes. It simply marks the table as unavailable or puts it in incomplete status."
CREATE INDEX XMT00.AMPAPX
  ON XMT00.CONTACT_TABL
   (START_TRN_ID          ASC)
  USING STOGROUP AXX001SG
  ....
Even If  we want to drop the unique index for that table, DB2 will allow to drop it, but the table will be unusable and it will be in incomplete status.
So, in conclusion this primary key and unique index goes hand-in-hand.!!!

2 comments:

  1. Nice info Deb.I didn't know that Unique index is a madatory thing if there is a primary key!!

    Sreenivas,
    www.mainframeinterview.com

    ReplyDelete
    Replies
    1. Hi Sreeni.. thanks!..
      Refer to this for better understanding:
      http://www-01.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/com.ibm.db2z10.doc.intro/src/tpc/db2z_uniqueindexes.dita

      If a unique index does not already exist on the columns of the primary key,
      then DB2 will create such an index automatically when a PK is defined.

      It is often desirable to first create the unique index yourself, and then
      create the PK after that, because if DB2 creates the index automatically for
      you it cannot be altered for various attributes such as percent free,
      cluster, allow reverse scans, etc.

      Delete