473,320 Members | 1,887 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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 35251
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: Kamil | last post by:
Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil
5
by: aj | last post by:
DB2 WSE 8.1 FP5 Red Hat AS 2.1 What is the difference between adding a unique constraint like: ALTER TABLE <SCHEMA>.<TABLE> ADD CONSTRAINT CC1131378283225 UNIQUE ( <COL1>) ; and adding a...
4
by: deko | last post by:
I have a Make Table query that creates a fairly large table. The Make Table query populates the new table with one AutoNumber field (which is taken form another unrelated table as part of the...
10
by: deko | last post by:
I understand it's possible to make a composite Primary Key by holding down the control key and selecting multiple fields, then right-clicking and selecting Primary Key. But I've heard that's not a...
4
by: shsandeep | last post by:
I have added primary key to my table using the 'ALTER TABLE' statement. Now, is it mandatory for me to use the 'CREATE UNIQUE INDEX' on primary key columns in order to enforce the primary key...
10
by: Laurence | last post by:
Hi there, How to differentiate between unique constraint and unique index? These are very similar but I cannot differentiate them? Could someone give me a hand? Thanks in advance
8
by: paii, Ron | last post by:
I have a table listing drawing numbers for jobs. It's primary key combines Job and numeric part of the drawing number. The structure allows the number part to repeat for each job. Job Dwg...
4
by: p175 | last post by:
What are the advantages / disadvantages of having primary keys in lieu of ordinary indexes ? If there are no foreign relationships defined, is it better to have a two column index allowing reverse...
6
by: Alvin SIU | last post by:
Hi all, I have a table in Db2 v8 like this: Team Name Role ------ -------- --------------------- A Superman Leader A Batman Member A WonderWoman Member B ...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.