ORA-01408: such column list already indexed

Oracle SQL-Developer generates DDL statements of already exising database tables (items). It's quite strange that the generated DDL statements can't be applied within a new database instance. Here's a simplified example of the DDL

CREATE TABLE AB ( "A" NUMBER(*,0), "B" NUMBER(*,0), "C" VARCHAR2(255 BYTE), CONSTRAINT "CHK_AB_A_NN" CHECK (A IS NOT NULL) ENABLE, CONSTRAINT "CHK_AB_B_NN" CHECK (B IS NOT NULL) ENABLE, CONSTRAINT "PK_AB" PRIMARY KEY ("A", "B") );
CREATE INDEX "IDX_AB_A" ON "AB"("A");
CREATE INDEX "IDX_AB_B" ON "AB"("B");
CREATE UNIQUE INDEX "PK_AB" ON "AB"("A", "B");

If I execute those statements within a new oracle instance, I get the error:

SQL-Fehler: ORA-01408: Diese Spaltenliste hat bereits einen Index 1. 00000 - "such column list already indexed"

What's the reason for this error?

2 Answers

The part:

CONSTRAINT "PK_AB" PRIMARY KEY ("A", "B")

is generating an index. Primary key constraint cannot exists without an index. However, the part:

CREATE UNIQUE INDEX "PK_AB" ON "AB"("A", "B");

is generating yet another index with the same columns. This is the reason for the error. Quite strange though, that the Oracle tool is generating a wrong script :) Maybe it's a bug.

Your primary key constraint in the CREATE TABLE statement automatically creates unique index on ("A","B"), since it's PRIMARY KEY. Error is then raised, since you try to re-create already existing UNIQUE INDEX on same columns.

UPDATE: I've tested it with Oracle SQL Developer 3.2.20.09 and the above mentioned problem is not present. Are you possibly using older version ?