RE: Unique Index vs. Primary Key

> -----Original Message----- > From: fprose@rocketmail.com [SMTP:fprose@rocketmail.com] > Sent: Friday, August 27, 1999 11:34 AM > To: informix-list@iiug.org > Subject: Unique Index vs. Primary Key > > Are there any speed, performance, or space issues associated with > indexing using the Primary Key constraint versus a 'plain old' index. > > For example: > > create table client > ( > prob_no INTEGER not null, > last_name CHAR(10) , > first_name CHAR(13) , > primary key (prob_no) constraint pk_1);> > > VERSUS: > > create table client > ( > prob_no INTEGER not null, > last_name CHAR(10) , > first_name CHAR(13) );> > create unique index client_pk on client (prob_no asc);> > > We don't require the "Primary Key" for referential integrity purposes > so it would appear to be simply a matter of documentation and the > prefered choice of PowerDesigner. I'd like to believe that 'an index > is an index', but since changing some structures over to the Primary > Key constraint we appear to be performing more I/O. > [Bill Raper] >>> Personally <<< I prefer primary keys. As you mentioned, the reason is primarily documentation. However, there is a significant twofold disadvantage to using a primary key "by itself" as opposed to using a unique index. The downside is the inability to (1) fragment the index for load balancing / performance and (2) to choose the dbspace in which your primary key will reside. This can be firmly tied to performance. This downside can be overcome if you first create your unique index, placing it in the appropriate dbspace and fragmenting it. Once this is done, you can create the primary key and it will utilize the existing unique index. As far as increased I/O being caused by dropping a unique index and creating a foreign key on the same columns, I simply do not know. Regards Bill