Forum Stats

  • 3,782,581 Users
  • 2,254,668 Discussions
  • 7,880,130 Comments

Discussions

SQL Developer 1.5.x BUG saving tables with "odd" named primary key

user256326
user256326 Member Posts: 4
edited Mar 17, 2010 9:50AM in SQL Developer
Problem: After upgrading to 1.5 (or 1.5.1), we are unable to make meta changes (DDL) to some tables due to the following error:

"Primary key constraint PK_<tablename> is defined identically to index <indexname>."

However that <indexname> IS the index enforcing the primary key, and can't be deleted.

The problem is evident if you compare the DDL generated on the "SQL" tab of the table:

in SQL Developer 1.2:
CREATE TABLE "AD"."AD_EXP_CHANGETYPE"
( "ID_CHANGETYPE" NUMBER(11,0) NOT NULL ENABLE,
"NAME" VARCHAR2(50 BYTE) NOT NULL ENABLE,
CONSTRAINT "PK_AD_EXP_CHANGETYPE" PRIMARY KEY ("ID_CHANGETYPE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADTAB" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADTAB" ;

SQL Developer 1.5.1:

CREATE TABLE "AD"."AD_EXP_CHANGETYPE"
( "ID_CHANGETYPE" NUMBER(11,0) NOT NULL ENABLE,
"NAME" VARCHAR2(100 BYTE) NOT NULL ENABLE,
CONSTRAINT "PK_AD_EXP_CHANGETYPE" PRIMARY KEY ("ID_CHANGETYPE")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADTAB" ENABLE
) PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADTAB" ;


CREATE UNIQUE INDEX "AD"."AD_PK_EXP_CHANGETYPE" ON "AD"."AD_EXP_CHANGETYPE" ("ID_CHANGETYPE")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 1048576 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT)
TABLESPACE "ADTAB" ;

Problem seems to be related to our index naming standard which are slightly different than default:
AD_PK_EXP_CHANGETYPE versus PK_AD_EXP_CHANGETYPE.

Additional environment info:
OS: Windows XP SP2
Java: 1.6.0_6
Oracle: Oracle Database 10g Enterprise Edition Release 10.1.0.5.0

Comments

  • Update: I was able to recreate the problem!

    1. Create a new table (I used TEST).
    2. Mark first column af primary key.
    3. Save the table.

    Notice the name of the primary key index created: (TEST_PK in my case)

    4. Edit the table.
    5. Under constraints change the name of the primary key constraint.
    6. Save the table.

    Notice that the index still has its original name.

    7. Edit the table.
    8. Change something.
    9. Attempt to save changes (Fails).

    Conclusion:
    Either SQL Developer should be aware of indexes with different name than a constraint,
    or when SQL Developer changes the name of a constraint with an index, both the constraint and the index should be renamed.
  • Emmm... BUMP!

    I was expecting some sort of feedback on this issue even though it's a small one...
    If anything is unclear please let men know.
  • -K-
    -K- Member Posts: 6,171
    If you want guaranteed follow-up, log a SR through Metalink, else hope a developer picks up the issue...

    Regards,
    K.
  • 672212
    672212 Member Posts: 1
    Any updates on this? SR opened?
    One of my developers started getting this errors lately..

    Thanks in advance,
  • 689580
    689580 Member Posts: 1
    Hi,

    I'm experiencing this error for practically every table when I select, Edit and chose the indexes.
    "index XX is defined identically to constraint XX"

    The only way to make the error message disappear is to close the table. I'm unable to make any schema modifications to the table.

    I noticed that someone was able to recreate the problem, but why is this occurring?

    any help to resolve this would be greatly appreciated!

    Cheers,

    RJ
  • 716274
    716274 Member Posts: 1
    I have the same problem and it is preventing me to work with ASP.Net when I go in the Grid View wizard I cannot generate update, insert, delete, etc and I do have a primary key in all my tables, I think its because the index and primary key arent named the same. How would I fix this ?
  • 480239
    480239 Member Posts: 16
    Same here ... am using SQL Developer Version 1.5.4. Build MAIN-5940
  • vilinski
    vilinski Member Posts: 7 Blue Ribbon
    Have same problem.

    SQL Developer Version 2.1.1.64
    Oracle 9i, Oracle 11GR1

    This problem is very disturbing, i can't modify tables anymore.
    Is there some solution?
This discussion has been closed.