Toad World® Forums

Show FK indexes?


#1

Good evening,

Is it possible that we can show the (IX#) on fields that are indexed based on the relationship and "Create Index to Foreign Key" value? This will allow us developers to see which fields are indexed in a table ... .:)

The reason for above …

I made the mistake of keeping that box checked, as well as creating an index on the table for the same field. This allowed me to see which fields are indexed. However, when running the generated script, oracle sprouted tons of ORA-01408 errors.

Now, if I remember my own indexes, and kept the relationship fk indexes in, the model does not show the indexed fields indicators (IX#) by their names, even though they are indexed …


#2

Hello Shannara,

If we understand well, you’d like to create in your model the indexes that are created during DDL script generation.
If you like to have these indexes directly in your model, please run the following script made by my co-worker Mario. Run the script in Scripting Window (Tools menu). The script will work for Oracle model.
See the script attached.

Note: See the fifth line.
var Model = app.Models.GetObject(0);
//The parameter in GetObject determines here with which model the script should work.
//0 is the first model listed in the Application View.
//1 is the second model listed in the Application View

Regards,

Vladka + Mario
CreateIndexForeignKey_Oracle.js (1.49 KB)


#3

Ah ok, before I run it, as I have such a huge model. This doesn’t actually create duplicate indexes on tables then? Only show that there are generated indexes?


#4

Hi,

Mario has enhanced the script. See the new one in the attachment.
Some details and information:

Yesteday’s script would create also indexes to those relationships to which indexes would not be created in DDL. So, please use this new script instead of the yesterday’s one.

  • In the new script, the same number of indexes will be created (indexes created by the script = indexes created in DDL.)
  • In the Log area, names of added indexes will be written.
  • Only indexes to relationships where option “Create Index to Foreign Key” will be created.
  • Duplicate indexes issue - The script verifies the duplicity by index names. Nevertheless, if you already have index of name “Index1” with column “a”, the script can create e.g. an index of name “IX_Relationship1” with column “a”.
    If you like to prevent from this, you can modify the script on your own or write us and we might modify it for you (however, please do not expect it to be immediately).

Thanks.

Important Note: Scripting Window in next Beta 3.1.3.55 will be available only if Expert Mode is turned on(Settings | Options).

Vladka + Mario

Message was edited by: vladka - added important note

Message was edited by: vladka
CreateIndexForeignKey_Oracle_new.js (1.67 KB)


#5

Thanks :slight_smile: I will end up having to modify it, because Oracle will error out now if I used the script Mario created :slight_smile:


#6

Just a note:
Please have a look at the DDL Script Generation dialog | Detail Settings tab | see the “Create Indexes to Foreign Keys” checkbox - it should be clear (should not be selected).

Regards,

Vladka