Oracle 10g index is defined identically to constraint

Eric Goldberg
Eric Goldberg used Ask the Experts™
on
We are seeing an Oracle Index error(no ORA number only text is returned) of
Index <index name> is defined identically to constraint <constraint name>
Investigating this with Oracle SQL Developer and Toad (so we know that this is not a bug in our development platform software), the primary key constraint has an index name say PK_TABLENAME. When we look at the list of indexes, there is an identical index called PK_TABLENAME. This is happening on many of the tables in the schema, but not all. I am guessing that the issue is not that there are two duplicate indexes, but that Oracle thinks there are two indexes because the constraints table and the indexes table both have entries. Anyone seen this issue and does anyone have a solution, short of exporting the tables, recreating them without keys, then applying the primary key and all other indexes after.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Top Expert 2009

Commented:
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.
Eric GoldbergEnterprise System Architect

Author

Commented:
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" ;
 
Top Expert 2009

Commented:
>>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.
Success in ‘20 With a Profitable Pricing Strategy

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Top Expert 2009

Commented:
Perhaps I am confused. How did you generate the DDL? Via Oracle Export? If so, then I am stumped.
Eric GoldbergEnterprise System Architect

Author

Commented:
[deleted duplicate post] -mrjoltcola / Zone Advisor
Eric GoldbergEnterprise System Architect

Author

Commented:
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" ;
 
Top Expert 2009

Commented:
>>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.

Eric GoldbergEnterprise System Architect

Author

Commented:
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
Enterprise System Architect
Commented:
The answer from Oracle support was basically to drop all indexes and constraints, export the tables, reimport the tables and recreate the constraints and indexes.
They said this was the "simplest" way without manipulating dba related tables, which they did not recommend.
So I'll close this thread now
Thanks for the ideas.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial