Oracle Index with key + ORA-01408

SQL> create table test ( no number);
Table created.

SQL> alter table test add constraint pkno primary key (no);
Table altered.

NOTE: Primary key or Unique key create Unique index automatically, so there is not user intervention required.

So If we trying to create index which already primary key or unque key created the we get following error.

SQL> create index testidx on test(no);

create index testidx on test(no)

*

ERROR at line 1:

ORA-01408: such column list already indexed

There is no requirement to create explicit index but if we want to more control over index then we can create index over primary /unique constraint column.

SQL> alter table test drop constraint pkno;
Table altered.

SQL> alter table test add constraint pkno primary key(no) using index (create index idxtest on test(no));

Table altered.

SQL> select index_name from user_indexes where table_name='TEST';
INDEX_NAME

------------------------------

IDXTEST

See also: http://download.oracle.com/docs/cd/B28359_01/server.111/b28310/indexes003.htm#sthref2025