CODEWITHSUNDEEP
sqloracle-databaseoracle-sqldeveloper
My-Name-Is
My-Name-Is

Reputation: 4942

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?

Upvotes: 11

Views: 47511

Answers (2)

tvm
tvm

Reputation: 3449

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 ?

Upvotes: 6

Alen Oblak
Alen Oblak

Reputation: 3325

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.

Upvotes: 21

Related Questions