Primary Key Vs Unique Index in DB2

[vj_dba]

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..inter nally 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.

[Serge Rielau]

Re: Primary Key Vs Unique Index in DB2

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/ondemandbusiness/conf2006/

[Mark A]

Re: Primary Key Vs Unique Index in DB2

"vj_dba" <vijay.ws@gmail .comwrote in message
news:1156428913 .395410.140120@ i3g2000cwc.goog legroups.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..inter nally 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).

[Knut Stolze]

Re: Primary Key Vs Unique Index in DB2

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