473,549 Members | 2,243 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Unique Index or PK ?

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 scans or primary
key ? Any performance or other benefits / issues ?

Many thanks.

Sep 1 '06 #1
4 5155

p175 wrote:
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 scans or primary
key ? Any performance or other benefits / issues ?

Many thanks.
Ignore, sorry . found a similar post .. siiigh DOH !!

Sep 1 '06 #2
"p175" <td******@hotma il.comwrote in message
news:11******** **************@ i3g2000cwc.goog legroups.com...
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 scans or primary
key ? Any performance or other benefits / issues ?

Many thanks.
Keep in mind that if you create the index first (before creating the PK),
then you can define reverse scans, cluster, etc and then when you create the
PK it will use the existing index with the attributes you want in the index.

Reverse Scans has "virtually" no overhead, and "may" have benefits in
certain situations. It really should be the default.
Sep 1 '06 #3

Mark A wrote:
Keep in mind that if you create the index first (before creating the PK),
then you can define reverse scans, cluster, etc and then when you create the
PK it will use the existing index with the attributes you want in the index.

Reverse Scans has "virtually" no overhead, and "may" have benefits in
certain situations. It really should be the default.
Now that I didn't know, so if I create an index, I can then alter the
table, adding a PK constraint that will assume the attributes of the
original index .. hmmm

Most useful, thanks.

Sep 1 '06 #4
"p175" <td******@hotma il.comwrote in message
news:11******** *************@p 79g2000cwp.goog legroups.com...
>
Now that I didn't know, so if I create an index, I can then alter the
table, adding a PK constraint that will assume the attributes of the
original index .. hmmm

Most useful, thanks.
A PK will create a unique index unless there is already a unique index on
the same columns, in which case no additional unique index is created. You
will get the following warning message:

Assume table TEST has no keys and no indexes to start with:

CREATE UNIQUE INDEX DB2INST1.TEST_I X1 ON DB2INST1.TEST (COL1 ASC);
DB20000I The SQL command completed successfully.

ALTER TABLE DB2INST1.TEST PRIMARY KEY (COL1);
SQL0598W Existing index "DB2INST1.TEST_ IX1" is used as the index for the
primary key or a unique key. SQLSTATE=01550

In the DB2 catalog (SYCAT.INDEXES) , the UNIQUERULE wil change from U
(Unique) to P (used for a Primary Key). If the PK is dropped, then the
unique index remains (only if you created it before the PK).
Sep 1 '06 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
602
by: Laphan | last post by:
Hi All I know you can set a row to be unique, but I want expand on this in that I want the DB schema to only disallow an entry if the row isn't unique across 3 fields. Is it possible? My DDL for this table is as follows: CREATE TABLE `WEBSTRINGS` ( `STRINGID` INT NOT NULL AUTO_INCREMENT,
3
27456
by: June Moore | last post by:
Hi, I would like to add a unique index that consists of two fields in a table. e.g. tbl_A (field1,field2) -- field1 & field2 Indexed and combination must be Unique. Can anyone tell me the actual sql syntax to create this index? Thanks, June.
5
10850
by: Kamil | last post by:
Hello What should I use for better perfomance since unique constraint always use index ? Thanks Kamil
9
2420
by: Rolf Kemper | last post by:
Dear Experts, I got stuck with the following problem and need your help. What I wnat to do is to get a set of distinct nodes. Before the distinct I have selected the multiple occourences already sucsessfully. However , the rest does not work as expected. Hope someone can help on that. Rolf
6
2084
by: Bob Stearns | last post by:
I was under the impression that the primary key had to be a unique index. Since I usually create my primary indices before my primary keys, in order to get the indices in the same schema as their tables, it is possible , by error, to create such an index without the unique attribute. DB2 UDB 8.1.5 Linux uses such an index for the primary key...
5
16695
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 unique index like:
10
14655
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
3
35318
by: vj_dba | last post by:
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...
6
4305
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 Alvin Leader
0
7548
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7986
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7504
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7832
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
5114
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3518
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3499
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1965
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
0
786
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

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.