Generate DDL is not generating primary key indexes

Toad Data Modeler
1

I am using Toad Data Modeler 5.2.4.25 and I am trying to generate a DDL script for my Oracle 11g R2 database. I have the following related settings:

  • In the settings of the primary key I have defined the name of the index as foo_pk_idx
  • In the Order of Generated Objects I have keys set to “After CREATE TABLE section”
  • In the settings for generating DDL, under the “What to Generate” tab, the Extended Value for Entities/Keys is set to “Drop and Create All Keys” and Entities/Indexes is set to Drop and Create.
    Toad Data Modeler is currently generating something like:

CREATE TABLE foo(
foo_col1_pk Integer NOT NULL,
foo_col2 Varchar(10) NOT NULL,
foo_col3 Varchar(20)
)
/

CREATE UNIQUE INDEX foo_uk1 ON foo (foo_col2) --this is a unique key that is not the primary key
/

ALTER TABLE foo ADD CONSTRAINT foo_pk PRIMARY KEY (foo_col1_pk)
USING INDEX foo_col1_pk_idx
/

I receive an error “SQL Error: ORA-01418: specified index does not exist” because the script does not generate the primary key index before the primary key constraint. Ideally I would like Toad to generate the primary key as:

ALTER TABLE foo ADD CONSTRAINT foo_pk PRIMARY KEY (foo_col1_pk)
USING INDEX
/

This would automatically name the index for me as foo_col1_pk. However, i would be fine if it would just include the index DDL for the primary key index. Could someone suggest how to do either?

2

Hi,

if you want to use “named” index, create the index in your entity. Another option you have is to not specify value in Name of Using Index and Oracle will create index of the same name as primary key.

Regards,

Vaclav & Mario

3

Vaclav,

You are right. If I leave out the index name then it generates:

ALTER TABLE foo ADD CONSTRAINT foo_pk PRIMARY KEY (foo_col1_pk)

/

Oracle does automatically create the unique index with the same name as the constraint. I mistakenly thought you had to have “USING INDEX” to automatically generate the index. My mistake. So this does resolve my issue. However, I am curious as to why Toad generated scripts for my unique constraints that were not associated with a primary key, but did not generate my named constraints for the primary keys. I had populated named indexes for every primary key in my model.

4

I guess what you are saying is that Toad doesn’t automatically generate the script for the constraint just because I populate the name of the constraint. I still have to create the unique index in the model as well.

5

Yes, this is correct. If you want to use named index, you must create the index manually in your model.

Regards,

Vaclav