Converting NOPI to PI table

Converting NOPI to PI table

There are 2 steps

a) Create a multi set table with the same structure and take a primary index on that table.

Create a multi set table New-tet(Party id integer, party name

Varchar(30)primary index(Party id)

b)Insert data into new- tet, drop tet table and rename new-tet to tet


i)Insert into new- tet select* from tet

ii) drop table tet

iii) Rename table new –tet to tet

Altering a primary index(it table is empty and it is not having NOPI) Alter table test modify primary index(Party name)


Default primary Index

If we don’t take and index on the table the system creats a primary index in the below process

  1.  The unique key(or) primary key column consider as UPI
  2.  IF No unique kdy (or) primary key it create on the first column NOPI.



If both primary key and unique key columns available it takes primary index(PI)on primary key column and (Unique primary index) and unique columns as USI(Secondary index)



Difference between primary index and primary key

Primary index(P.I)

Primary key (P.K)


Primary index mandatory

Primary key optional

Max 64 Columns combination

 No limit on no. of columns to composite

effects data distribution

Doesn’t effect data distribution

can take duplications and null

 Doesn’t allow null and duplicates.
 physical mechanism Logical mechanism

Cannot be drop

Cannot be drop

Queries stores and retrieve faster PI etc….