Script to identify Keys (Primary, Foreign and Primary Foreign)

Toad Data Modeler
1

I am trying to mimic the action that occurs when one changes the Physical Model relationships from Primary Foreign **Key **to a Foreign Key.

Why? My data model will soon have hundreds of tables. I don’t wish to perform this action by hand.

The actions seem to be:

  1. The relationship line between the Entities change (Identifying = Solid line, Non-Identifying = Broken line) NB: Code for this below

  2. the associated Primary Foreign Key attributes within an Entity are ‘changed’ to be Foreign Key within (on, across, over) those attributes.****NB: I don’t know how to do this.

  3. An associated index is created on the Foreign Key. NB: I don’t know how to do this.

I have located code to identify keys object (though I could not find reference to Keys in the [Script Editor | Expert Mode] Reference Guide) However, it appears that the keys only refers to Primary Keys, not Foreign Keys or Primary Foreign **Keys. ** I am aware that in TOAD Data Modeler:

Foreign Keys are shown in Green Font Color
Primary Foreign Keys are identified by Blue Font Color
Primary Keys are shown in Red Font Color

Qu 1: Using code; How does one identify when an Attribute has a setting of Foreign or **Primary Foreign. **

Qu 2. Using code: How to modify an Attribute setting to (Enforce a) Primary Key, Foreign Key or Primary Foreign Key. (obviously a Attribute may only be in one of those states)

Qu 3. Using code: How does one create a new Index, containing not the Primary Key (of that Entity), but the Foreign Key (of that entity). one Attribute, not compounded attributes.

I am aware that there are times when I will want to retain Primary Foreign **Key **over a Foreign Key, but I believe I can program for that component.

Thank you.

//Code to Change Relationship types from Identifying to Non-Identifying

for (e=0; e<Model.Relations.Count; e++)
{
iterObject = Model.Relations.GetObject(e);
if (iterObject.Identifying)
{
iterObject.Identifying = false; //An associtated index will not be created, but you really should create an index to support this
//Do something here to create an index. I don’t know what as of yet.
}
}

//Code to identify if an Entity contains a Primary Key

for (e=0; e<Model.Entities.Count; e++)
{
Entity = Model.Entities.GetObject(e);
for (k=0; k<Entity.Keys.Count; k++)
{
Key = Entity.Keys.GetObject(k);
if (Key.IsPrimaryKey)
{
//Key.Name = “PK_”+Entity.Name;
Log.Information("Primary Key : " + Entity.Name );
}
}
}

2

Hello Andrew,

change type of relationship is not easy, because there is difficult problem of migrate attributes(one simple change can influence a lot of entities).

In next version(and Beta) will be public methods available from scripting

  • Relation.MakeIdentifying()

  • Relation.MakeNonIdentifying()

Qu 1: Using code; How does one identify when an Attribute has a setting of Foreign or Primary Foreign.

attr.IsPrimaryKey - Attribute is Primary key. It can be Foreign or not.

attr.IsFK() - Attribute is Foreign Key. It can be Primary or not

Qu 2. Using code: How to modify an Attribute setting to (Enforce a) Primary Key, Foreign Key or Primary Foreign Key. (obviously a Attribute may only be in one of those states)

It dependent on Relationship type. You can do something like this, but there is missing propagation for next level entities

Rel.Identifying = false;
var attr = ent.Attributes.GetObject(0);
var Key = ent.PK;
var keyItem =Key.KeyItems.GetObject(0)
keyItem.Delete();

Qu 3. Using code: How does one create a new Index, containing not the Primary Key (of that Entity), but the Foreign Key (of that entity). one Attribute, not compounded attributes.

var index = ent.CreateNewObject(2012);//OT of Index
var indexItem = index.CreateNewObject(2013);//OT of index item
indexItem.LinkObject( attr );

Daril