PK's index not created correctly in script

Toad for Oracle & DevOps Toolkit
1

Howdy!

In 13.2.0.258, a script generated for a table that has a PK with a different name than its supporting index does not generate the index with the correct name. In my case, it's important as the ERP system (JDEdwards) that creates all database objects refers to them by specific names. I can supply a case in a few hours, but wanted to get this out here before I forgot...

Thanks!
Rich

2

Hi Rich,

I just made this table and index and it came out OK. Let me know what you did differently.

CREATE TABLE EMP
(
  EMPNO     NUMBER(4),
  ENAME     VARCHAR2(10 BYTE),
  JOB       VARCHAR2(9 BYTE),
  MGR       NUMBER(4),
  HIREDATE  DATE,
  SAL       NUMBER(7,2),
  COMM      NUMBER(7,2),
  DEPTNO    NUMBER(2)
);


CREATE UNIQUE INDEX IDX_EMP ON EMP
(EMPNO);

ALTER TABLE EMP ADD (
  CONSTRAINT PK_EMP
  PRIMARY KEY
  (EMPNO)
  USING INDEX IDX_EMP
  ENABLE VALIDATE);

-John

3

Hey John,

Here's my table, index, and PK:

CREATE TABLE MY_TABLE
(
MY_COL NUMBER,
MY_DATA NCHAR(2)
)
TABLESPACE USERS;

CREATE UNIQUE INDEX MY_INDEX ON MY_TABLE
(MY_COL)
TABLESPACE USERS;

ALTER TABLE MY_TABLE ADD (
CONSTRAINT MY_PK
PRIMARY KEY
(MY_COL)
USING INDEX MY_INDEX
ENABLE VALIDATE);

And here's the result I get from the RHS script tab in the SB:

CREATE TABLE MY_TABLE
(
  MY_COL   NUMBER,
  MY_DATA  NCHAR(2)
)
TABLESPACE USERS
NOCACHE
RESULT_CACHE (MODE DEFAULT)
/


--  There is no statement for index MY_INDEX.
--  The object is created when the parent object is created.


ALTER TABLE MY_TABLE ADD (
  CONSTRAINT MY_PK
  PRIMARY KEY
  (MY_COL)
  USING INDEX
    TABLESPACE USERS
  ENABLE VALIDATE)
/


--  There is no statement for index MY_INDEX.
--  The object is created when the parent object is created.

In this case, the index needs to be named "MY_INDEX", but the script will create one called "MY_PK" instead.

Thanks!
Rich

4

huh, weird, that's basically the same that I did but with a different result. I wonder if there is something about DB that's doing something different in data dictionary.

What's the DB version?

5

12.1.0.2 The XML behind DBMS_METADATA did change between 11.2 and 12.1. There's a bug 20217704 to fix at least one of the anomalies, but I decided not to install it...

Thanks!
Rich

6

Hi Rich,

Sorry for the delay.

From SB RHS Script Tab, go to the script options dialog, then "Tables" tab. Uncheck "No constraint indexes"

image
image.png619×689 36.3 KB

That ought to get it.

7

Doh! I saw that and had changed it, but it made no difference. I didn't record my steps, but after taking a look at it again, I think a refresh of the RHS may have been needed post-change.

Thanks much, John! :smiley:

Rich

1 Like