MVSFORUMS.com

NASCAR9 · Posted: Thu Nov 17, 2005 4:45 pm Post subject: Primary Key Vs Unique Index

Is there any performance or less overhead to be gained by defining a table with PRIMARY KEY vs. just creating a UNIQUE INDEX?

kolusu · Posted: Thu Nov 17, 2005 5:35 pm Post subject:

NASCAR9,

When you create a table with PRIMARY KEY , it's definition is incomplete until the primary index is created. Actually the Table is unavailable until the index is created.

Defining a column has advantages that it is a unique key and it cannot contain NULLS.

You can achieve the same concept by defining the column as not null and creating an Unique Index on it.

Hope this helps...

Cheers

Kolusu
_________________
Kolusu
www.linkedin.com/in/kolusu

NASCAR9 · Posted: Thu Nov 17, 2005 5:48 pm Post subject:

Thanks kolusu,
This is what I suspected. I'm in the process creating many new tables.
I want to make sure they are defined in the most efficient way possible. Some of the questions I ask may seem obvious to many on this board, but keep in mind we are a very young DB2 shop. (Less than 6 years)
_________________
Thanks,
NASCAR9

kolusu · Posted: Thu Nov 17, 2005 6:22 pm Post subject:

Dip · Beginner Joined: 24 Mar 2006 Posts: 27 Topics: 14

Can we define an Unique INDEX on two fields/keys where one of them can be NULL?

Thanks!
Dip.

Dip · Beginner Joined: 24 Mar 2006 Posts: 27 Topics: 14

Just now i read in a discussion forum that DB2 has no problem or restriction with NULLs in non primary key-index. Can someone confirm it?

Also want to know, if there will be any performace degradation if 5% of the total records having NULL in one of the key field which is part of the Unique index?

Thanks!
Dip.