Create Index to Foreign Keys checkbox

Toad Data Modeler
1

When I click on "“Help” in the Relationship dialgoue, the example shows a checkbox for generating foreign keys. But this isn’t present on the dialogue box (print example attached in file). I’m using file version 4.0.6.13

relationship.doc (210 KB)

2

Hi,

this option is supported only by some databases. See above the pic that the example is from Oracle 10g. Unfortunately, SQL Server dbs do not support it.

Regards,
Lukas

3

Maybe it is not clear enough to me. Why it depends on database?
I found this checkbox is also missing for mysql.

I’m sure CaseStudio has this feature.
Syntax of create foreign key in MySQL also allows to define index to create (or indexes my be created separately like in CS2).

best regards

4

It depends on database in that respect that some dbs, like MySQL, create the indexes for FKs automatically, either on creating FK in the db or when reverse engineering the db.
Anyway, in CaseStudio, the feature wasn’t there at first, than it was added and then removed again.

Regards,
Lukas

5

Hi Dawn,
To have the indexes for FKs created, you can use the following script ran in Scripting Window:

function main()
{
var i, j, Relation, ChildEntity, IndexName, Index, IndexItem, Attribute;
var app = System.GetInterface(“Application”);
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, etc.
Model.Lock();
for(i=0; i<Model.Relations.Count; i++)
{
Relation = Model.Relations.GetObject( i );

ChildEntity = Relation.ChildEntity;
IndexName = "IX_"+Relation.Name;
if (ChildEntity.Indexes.GetObjectByName(IndexName) == null)
{
  Index = ChildEntity.CreateNewObject( 2012 );
  //Create new Index (2012 is object type of PERIndex)
  Index.Name = IndexName;
  Index.Caption = IndexName;
  for (j=0; j<Relation.ForeignKeys.Count; j++)
  {
    Attribute = Relation.ForeignKeys.GetObject(j).AttrChild;
    IndexItem = Index.CreateNewObject( 2013 );
    //Create new Item of Index (2013 is object type of PERIndexItem)
    IndexItem.Attribute = Attribute;
  }

  Log.Information("Index "+IndexName+" created on table "+ChildEntity.Name);
}

}
Model.UnLock();
}

On line 5: you can change the model you want to make changes in
On line 15: you can change the name of generated index

Regards,
Lukas

6

Yeah. MySQL5 is able to create index if there is no required one yet.
On the other side, postgresql doesn’t require indexes on FKs at all.

So, in both cases, auto-create index feature may be considered as unneeded.

That’s why I don’t understand why postgresql has this feature when mysql hasn’t.

7

Hi Michal,

PostgreSQL has this feature because it was implemented on demand of a customer. There was no other reason.

Regards,
Luaks

8

I understand.
Is there any chance to implement this feature to other dbs? (Of course if considered that it does make sens)

best regards

9

Hi,

we will check the rest of dbs and let you know.

Regards,
Lukas

10

Lukas,

Thanks for the script! It’s much appreciated.

Regards,
Dawn

11

You’re welcome, Dawn.

Lukas

12

Hi Michal,

I’ve just created change request CR 90056 to add the functionality to other databases.

Regards,
Lukas

13

Thanx