468,294 Members | 1,807 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,294 developers. It's quick & easy.

Primary Key Vs Unique Index in DB2

Hi Group,
I have a Primary key in my table. It's clear Primary key wont allow
duplicates, this primary key creates one index for retrival. Suppose if
my table is having a Unique index also. Then what is the exact
difference between the Primary key and the Unique index?
Also which on data retrival..internally it uses the Primary key index
or the Unique index...also which method is faster? Kindly excuse if
concept is wrong...

Actually I want the clear idea about the Unique index and the Primary
Key....

Thanks in advance,
Vijay.

Aug 24 '06 #1
3 33452
A primary key requires all columns to be not nullable.
Also when you define an RI constraint without specifying the unique key
columns of the parent DB2 will assume you reference the primary key.

I think that's pretty much it. From an optimization case both utilize
unique indexes and that's what really counts.
I think for the optimizer a unique index on not nullable columns, a
unique constraint on not nullable columns and a primary key are all the
same.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

IOD Conference
http://www.ibm.com/software/data/ond...ness/conf2006/
Aug 24 '06 #2
"vj_dba" <vi******@gmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.com...
Hi Group,
I have a Primary key in my table. It's clear Primary key wont allow
duplicates, this primary key creates one index for retrival. Suppose if
my table is having a Unique index also. Then what is the exact
difference between the Primary key and the Unique index?
Also which on data retrival..internally it uses the Primary key index
or the Unique index...also which method is faster? Kindly excuse if
concept is wrong...

Actually I want the clear idea about the Unique index and the Primary
Key....

Thanks in advance,
Vijay.
If you already created a PK, and then try to create a unique index on the
same columns, DB2 will tell you that a unique index already exists on those
columns, and it will not create a second index.

If you create a table without a PK, then create a unique index, then alter
the table to create a PK on the same columns as the unique index, DB2 will
tell you that it is using the existing unique index for the primary key
(this is a warning message only). This can be useful if you want to define
the index as clustering, change the percent free, etc, because you cannot
alter an index in DB2 for LUW once it is created (unlike DB2 for z/OS).
Aug 24 '06 #3
vj_dba wrote:
Hi Group,
I have a Primary key in my table. It's clear Primary key wont allow
duplicates, this primary key creates one index for retrival. Suppose if
my table is having a Unique index also. Then what is the exact
difference between the Primary key and the Unique index?
It's mostly a conceptual difference. A primary key is a constraint. A
unique index is, well, an index. DB2 uses unique indexes to implement
primary keys.

--
Knut Stolze
DB2 Information Integration Development
IBM Germany
Aug 28 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by shsandeep | last post: by
10 posts views Thread by Laurence | last post: by
8 posts views Thread by paii, Ron | last post: by
4 posts views Thread by p175 | last post: by
reply views Thread by NPC403 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.