By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,070 Members | 1,237 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,070 IT Pros & Developers. It's quick & easy.

Primary Key Vs Unique Index in DB2

P: n/a
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
Share this Question
Share on Google+
3 Replies


P: n/a
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

P: n/a
"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

P: n/a
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.