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.

There is no performance or space or speed gain or loss from using either a unique index or primary key or even unique constraint the latter two create the unique index to do the actual constraint check. I've never noticed any significant change in I/O activity due to using primary keys or unique constraints, but that don't mean it isn't so. Art S. Kagel FProse wrote: > > 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.

at least on 7.23, creating PK seems to take significantly longer to build ...even if unique idx was created first, declaring the PK constraint seems to take decent amount of time. unique idx will accept nulls, PK won't ... as for performance after idxs/pk has been build, i've never noticed difference. Art S. Kagel <kagel@bloomberg.net> wrote: > There is no performance or space or speed gain or loss from using either > a unique index or primary key or even unique constraint the latter two > create the unique index to do the actual constraint check. > I've never noticed any significant change in I/O activity due to using > primary keys or unique constraints, but that don't mean it isn't so. > Art S. Kagel > FProse wrote: >> >> 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.