Oracle 10g index is defined identically to constraint

Please show the DDL. It is likely a bug in the tool that generates your DDL. I saw someone else report this on OTN regarding SQL Developer. I don't use SQL Developer to create tables and have never run into this error.

Also, just in case you didn't know, Oracle automatically creates indexes on your PRIMARY KEY and UNIQUE constraints for you. When you create a constraint with the explicit syntax:

   alter table t add constraint pk_t primary key (id);

Oracle will automatically create an index for you named PK_T

I prefer to let Oracle create the primary key index automatically by using line PRIMARY KEY() syntax, or using an ALTER TABLE ADD CONSTRAINT. You don't need a separate "CREATE INDEX" statement.

There are various options for defining the primary key index inline or with alter table. If you let us see the DDL we can help more.

We checked with both SQL Developer and TOAD, so I don't think this is the reported SQL Developer issue.
The partial DDL for one of the tables (I've left out the other indexes and the trigger code) for example is as follows. You can see that the DDL has both a Primary Key using index and an identical unique index. This is not the way the table was created, but it is the way it is looking now when I do a DDL export.
Doing a select on USER INDEXES and USER CONSTRAINTS shows that the index appears in both tables, when it should only be part of the CONSTRAINTS table.


  CREATE TABLE "TSP"."ED_VERSIONS"
   (      "VERSION_ID" NUMBER(10,0) NOT NULL ENABLE,
      "RECORD_ID" NUMBER(10,0) NOT NULL ENABLE,
      "REND_ID" NUMBER(10,0) NOT NULL ENABLE,
      "FILE_LOCATION" VARCHAR2(2000 BYTE),
      "LONG_NAME" VARCHAR2(255 BYTE),
      "FILE_TYPE" CHAR(4 BYTE),
      "FILE_NAME" CHAR(64 BYTE),
      "FILE_SIZE" NUMBER(19,0),
      "FILE_INFO" VARCHAR2(255 BYTE),
      "CREATE_DATE" DATE,
      "MOD_DATE" DATE,
      "FILE_CHECKSUM" CHAR(32 BYTE),
      "VCVERSION" CHAR(16 BYTE),
      "CHKINDATE" DATE,
      "THUMBNAIL" LONG RAW,
       PRIMARY KEY ("VERSION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_INDX"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_DATA" ;

 
  CREATE UNIQUE INDEX "TSP"."PK_ED_VERSIONS" ON "TSP"."ED_VERSIONS" ("VERSION_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_INDX" ;
 

>>Doing a select on USER INDEXES and USER CONSTRAINTS shows that the index appears in both tables, when it should only be part of the CONSTRAINTS table.

No, that is incorrect. When you create a PRIMARY KEY or UNIQUE constraint in Oracle, the index will be created for you. So you should ALWAYS see entries in both USER_INDEXES and USER_CONSTRAINTS for those 2 types.

Your DDL above is incorrect, in that it creates the table with a primary key, then tries to create a unique index on the same column after the primary key already exists. While it is ok to create an index first, and then create a constraint using that index, it is not ok to do the reverse, because Oracle has already done the work and the index object exists.

Use this to see what Oracle's thinks the DDL is.

set long 20000
select dbms_metadata.get_ddl('TABLE', 'ED_VERSIONS');
select dbms_metadata.get_ddl('CONSTRAINT', 'PK_ED_VERSIONS');
select dbms_metadata.get_ddl('INDEX', 'PK_ED_VERSIONS');


Just because the tool is generating the DDL doesn't mean that is correct. If the tool is somehow confused and doesn't associate the INDEX with the CONSTRAINT it could generate misleading / incorrect DDL.

Perhaps I am confused. How did you generate the DDL? Via Oracle Export? If so, then I am stumped.

[deleted duplicate post] -mrjoltcola / Zone Advisor

My last post had some formatting problems, so I am reposting:
The DDL was generated using SQL Developer which can generate the DDL for the objects.
I agree that this does not look correct. I re-ran the DDL as suggested in the previous post. The actual name for the primary key constraint was an Oracle generated name.

select dbms_metadata.get_ddl('TABLE', 'ED_VERSIONS') from dual;
select dbms_metadata.get_ddl('CONSTRAINT', 'SYS_C004900') from dual;
select dbms_metadata.get_ddl('INDEX', 'PK_ED_VERSIONS') from dual;

and got the following.
CREATE TABLE "TSP"."ED_VERSIONS"
   (      "VERSION_ID" NUMBER(10,0) NOT NULL ENABLE,
      "RECORD_ID" NUMBER(10,0) NOT NULL ENABLE,
      "REND_ID" NUMBER(10,0) NOT NULL ENABLE,
      "FILE_LOCATION" VARCHAR2(2000 BYTE),
      "LONG_NAME" VARCHAR2(255 BYTE),
      "FILE_TYPE" CHAR(4 BYTE),
      "FILE_NAME" CHAR(64 BYTE),
      "FILE_SIZE" NUMBER(19,0),
      "FILE_INFO" VARCHAR2(255 BYTE),
      "CREATE_DATE" DATE,
      "MOD_DATE" DATE,
      "FILE_CHECKSUM" CHAR(32 BYTE),
      "VCVERSION" CHAR(16 BYTE),
      "CHKINDATE" DATE,
      "THUMBNAIL" LONG RAW,
       PRIMARY KEY ("VERSION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_INDX"  ENABLE
   ) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_DATA" ;
 

  ALTER TABLE "TSP"."ED_VERSIONS" ADD PRIMARY KEY ("VERSION_ID")
  USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_INDX"  ENABLE;
 


  CREATE UNIQUE INDEX "TSP"."PK_ED_VERSIONS" ON "TSP"."ED_VERSIONS" ("VERSION_ID")
  PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
  STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
  PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
  TABLESPACE "DAMDB_INDX" ;
 

>>The DDL was generated using SQL Developer which can generate the DDL for the objects.

Which is why I think the bug is in SQL Developer.

I don't think you have a problem in your database. Take SQL Developer out of the loop...

1) Export the schema with Oracle then generate a DDL file from that:

  exp user/pass rows=n file=schema.dmp
  imp user/pass show=y file=schema.dmp indexfile=ddl.txt

  View ddl.txt

2) Also you could try generating DDL for the schema with Toad, or some other tool.
3) Open an SR with Oracle and report the problem with SQL Developer.

However, what I posted was generated using the following SQL
elect dbms_metadata.get_ddl('TABLE', 'ED_VERSIONS') from dual;
select dbms_metadata.get_ddl('CONSTRAINT', 'SYS_C004900') from dual;
select dbms_metadata.get_ddl('INDEX', 'PK_ED_VERSIONS') from dual;
which is not SQL Developer.
So I am still stumped.
Our DBA will be opening an SR with Oracle. I agree that this may just be something masquerading as a problem, however, even in Toad, if we try to alter the table index, we get the error about identical index to a constraint