Difference between primary key and index key

K.BHANU KRISHNA · New User Joined: 20 Dec 2006 Posts: 1 Location: chennai

what is the difference between primary and index key?
i had so much of confusion in this.please clear my doudt.

khamarutheen · Active Member Joined: 23 Aug 2005 Posts: 677 Location: NJ

Primary key is a unique key that is a part of the definition of a table. Where there wont be any null values ...

index key is the set of columns in a table that is used
to determine the order of index entries. This key may hold null values ...

DavidatK · Posted: Wed Dec 20, 2006 9:24 pm

Hello bhanu

I will assume this is a DB2 question, not JCL. Should post in the DB2 forum.

In a DB2 table, a Primary Key uniquely identifies the row, no two rows can have the same Primary Key value. (A Primary Key is not required, but you can only have 1).

An Index Key can be unique, or not. In an Index Key you can have multiple Rows with the same Index Key Value.

An example.

Employee table;

Employee_Number
Last_name
First_Name
Department
SSN

The Primary key could be Employee_Number, There can only be one employee with this number.

Index_Key_1 could be Department, There can be many Employees in the same department.

Index_Key_2 could be SSN (U.S. Social Security Number). This is not the Primary Key, but it is Unique, so you would declare this Index Key as being Unique.

Index_Key_3 could be a concatenation of Last_Name/First_Name. This could result in multiple rows having the same Index Key Value.

Dave