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))
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
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.!!!