How to Create Relationship with Script?

Toad Data Modeler
1

How does one build a relationship via scripting on a Parent's Alternate Key?

With scripting I want to emulate the behavior of Parent Key combobox in the Relationship dialog form where if one changes the Parent Key from the Primary Key to some Alternate Key, then the foreign key attribute(s) change correspondingly.

The following gives an idea of my failed attempts.

Suppose:
Entity5 has attributes id (PK) and ak (AK).
Entity6 has no attributes.

function main()
{
       EntParent = Model.Entities.GetObjectByName("Entity5");
       AttrParentLoc = EntParent.Attributes.GetObjectByName("ak");
       EntChild = Model.Entities.GetObjectByName("Entity6");
       AttrChildLoc = EntChild.Attributes.GetObjectByName("ak");
       KeyAK = EntParent.Keys.GetObjectByName("AK");
       Rel = Model.AddLinkObject(2004, EntParent, EntChild); //2004 is object type of PERRelation
       Rel.Name = "R_"+EntChild.Name+"_"+EntParent.Name
       Rel.Caption = Rel.Name;
       Rel.Key = KeyAK; //***   
       Fks = System.CreateObject('List');
       //Fk = System.CreateObject('PERForeignKeyOR12R2');
       //Fk = System.CreateObject('PERForeignKey');
       //Fk = Model.CreateNewObject('PERForeignKey');
       //Fk = Rel.ForeignKeys.GetObject(0).CreateNewObject(2014);
       Fk = Rel.CreateNewObject(2014);
       Fk.SetAttributes(AttrParentLoc,AttrChildLoc);
       Fks.Add(Fk);
       //Fk_id = Rel.ForeignKeys.GetObject(0);
       //Rel.RemoveObjectLink(Fk_id); //doesn't remove id
       //Rel.RemoveObjectLink(Rel.ForeignKeys); //doesn't remove id
       Rel.SetLinkedObject("ForeignKeys",Fks); //still has both id and ak, which doesn't make sense because Fks is a new object...
       //Rel.ForeignKeys.Remove(Fk_id); //leaves id as an attribute in Entity6.
}

Each of the commented out lines that tries to create the PERForeignKey object throws an error and the PERForeignKey object is not created.

How do I create a new PERForeignKey object? If I do, Fk = Rel.CreateNewObject(2014), then the original foreign key (id) remains in the list of foreign keys (ak is added, but id is still in the list of foreign keys). How to delete a foreign key from a list of foreign keys? I tried Remove(Fk_id), which removed id from the list of foreign keys, but left id in Entity6.

Is it possible for the relationship to automatically create the child attribute(s) based upon the Relationship's Parent Key (e.g. line ***)?

What am I missing?

What is the recommended way to create a relationship that is not based on the primary key from a script?

Thanks in advance.

2

Hello,
unfortunately this is no so easy. Because when you change key in relationship you need to change all migrated attributes. In TDM is function that do that, unfortunately it is not accessible from scripting. So I created task to our system with number TMB-2519 to do it (you can track this in release notes).
I propose some work around below, but it is not perfect.

function main(){
   var parentEnt = Model.Entities.GetObject(0);
   var childEnt = Model.Entities.GetObject(1);
   //Create a new Relationship
   Rel = Model.AddLinkObject(2004, parentEnt, childEnt);     
                               
   //Remove link from old relationship key
   Rel.Key.Relations.Remove(Rel);         
   //Add link to alternate key, in this case second key in list, but you can use method GetObjectByName
   Rel.Key = parentEnt.Keys.GetObject(1);
   //Delete old FKs from relationship
   var FK;    
   for(i=0;i<Rel.ForeignKeys.Count;i++)
   {  
     FK = Rel.ForeignKeys.GetObject(0);
     FK.Delete(); 
   }   
   Rel.Identifying = false;
     
       
   //Create New FKs in the relationship    
   var newAttr;
   for(i=0;i<Rel.Key.KeyItems.Count;i++)
   {                   
     KeyItem = Rel.Key.KeyItems.GetObject(i); 
     FK = Rel.CreateNewObject(2014);//Create FK
                                             
     //Try to find attribute
     newAttr = childEnt.Attributes.GetObjectByName('AChild');
     if (newAttr==null)
     {                      
       //if attribute is not find, than it will be created 
       newAttr = childEnt.CreateNewObject(2003);
       newAttr.Migrated = true; //New createed attribute will be deleted when relationship will be deleted
     }
     else
     {
       newAttr.KeepForeignKey = true; //Attribute will not be deleted when relationship will be deleted
     }   
     
     FK.SetAttributes(KeyItem.Attribute, newAttr);
     //There are place for some procedure to switch data type and others parameters of parent attribute to child attribute  
   }
   
}

Regard
Daril

3

Hi Daril,

I see this has been implemented in version 7.2.

Can you please provide us with an example usage?

Thanks

EDIT:
Also, I'm not sure about the meaning of "Migrated" flag, it seems just a !KeepForeignKey alias.
And newly create attributes are anyway deleted on relationship deletion even if the "Migrated" flag is not set.