Primary Keys and Unique Indicies

[sho...@pacificnet.net]

I'm curious if one should expect better performance by using a primary key
as opposed to an index. ie

ALTER TABLE closed ADD CONSTRAINT PRIMARY KEY (casenmbr) CONSTRAINT closed_key;
vs
CREATE UNIQUE INDEX closed_idx on closed (casenmbr);

I'm interested in both 5.x and 7.x engines, SE and On-Line

Thanks

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
Scott Holmes <sho...@pacificnet.net> Informix Applications
4GL -- SQL -- New Era
http://www.pacificnet.net/~sholmes

---- There are more things in heaven and earth, Horatio, ----
---- Than are dreamt of in your philosophy. ----
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -

[Nils Myklebust]

sho...@pacificnet.net wrote:

:I'm curious if one should expect better performance by using a primary key

:as opposed to an index. ie
:
:ALTER TABLE closed ADD CONSTRAINT PRIMARY KEY (casenmbr) CONSTRAINT closed_key;
:vs
:CREATE UNIQUE INDEX closed_idx on closed (casenmbr);
:
:I'm interested in both 5.x and 7.x engines, SE and On-Line

Every table needs a primary key so you allways have to define that. If
you don't you will get into trouble some day even if you are quite
sure right now you won't (been there, know that - bad experience). Of
course a unique index is kind of a surrogate, but that will allow one
single row with a null value if you haven't also defined the column(s)
involved as not null.

However you should for various reasons allways do as follows:

CREATE UNIQUE INDEX closed_idx on closed (casenmbr);

ALTER TABLE closed ADD CONSTRAINT PRIMARY KEY (casenmbr)
CONSTRAINT closed_key;

That is you should never let the definition of the primary key create
an index with an Informix supplied name (that will happen if there is
no previously defined unique index on the primary key column(s)).
One reason for this is you can later cluster the table on this index.
You can also easily drop and recreate it. Additionally some PC
products like MS Access doesn't like the automatically created index
names that start with a space.

As to performance the index is exactly the same however it is created
so when it's used there is no difference. There may be some issues
with the optimiser in selecting access path related to primary keys. I
don't know any, but if they are there they could be nothing but an
advantage.

Nils.My...@idg.no
NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway
My opinions are those of my company
The Informix FAQ is at http://www.iiug.org