At 01:54 AM 20/11/2003 +0000, you wrote:

>Hi All,
>
>FB 1.5 RC 7
>
>I am using PowerDesigner as my modelling tool and generate the following
>CREATE TABLE ddl:
>
>create table CDS_SYSTEM (
>NAME VARCHAR(100) not null,
>VALUE VARCHAR(100) not null,
>constraint PK_CDS_SYSTEM primary key (NAME)
>);
>
>create unique asc index CDS_SYSTEM_PK on CDS_SYSTEM (
>NAME
>);
>
>My question is since there is already a primary key, is the unique index
>still needed?

No, definitely not.

>
>What is the benefit of having unique index since we already got a primary
>key in the table ?

No benefit; and in some cases, having a duplicated index will prevent the
optimizer from using an index at all, when it should.

Also think seriously about abandoning that varchar(100) as a primary key
and using a generated integer as a surrogate. Then you can have a PK *and*
a unique index on the Name column as well (albeit with limitations on total
byte size which will affect you if the character set is not NONE and you
are using a collation sequence).

Modelling tools are ok for logical data modelling but they don't provide
you with sensible, atomic physical key structures...

heLen