468,294 Members | 1,794 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.

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 4593

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******@hotmail.comwrote in message
news:11**********************@i3g2000cwc.googlegro ups.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******@hotmail.comwrote in message
news:11*********************@p79g2000cwp.googlegro ups.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_IX1 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 discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Laphan | last post: by
9 posts views Thread by Rolf Kemper | last post: by
6 posts views Thread by Bob Stearns | last post: by
10 posts views Thread by Laurence | last post: by
3 posts views Thread by vj_dba | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by Teichintx | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.