Toad World® Forums

Relationships naming convention macro

Hi,

TDM version : 6.0

I am writing a macro to enforce naming conventions. I could successfully implement it for Primary keys, alternate keys, unique keys etc. I could also implement it for Relationships, however, I am stuck on how to find the column name of the parent table it is referring to.

Let me show an example of Emp-Dept tables to explain clearly. Let’s say I have the following relationship for which I am checking the naming convention as “dept_deptno_emp_deptno_fk

Example :

ALTER TABLE “scott”.“dept” ADD CONSTRAINT “dept_deptno_emp_deptno_fk” FOREIGN KEY (“deptno”) REFERENCES “scott”.“emp” (“deptno”);

The foreign key and the column name it references are same “deptno”. But let’s say the names are different, in such case I need the foreign key as well as the column name it is referring to, since the naming convention rule for relationships I need is:

ChildEntity_ForignKey_ParentEntity_ParentKey_FK

Macro:

for (i = 0; i < model.Relations.Count; i++)
{
obj = model.Relations.GetObject(i);
relationship = null;
if (obj.ObjectType == 2504) // relation line selected on
{
relationship = obj.ParentBase;
}
else if (obj.ObjectType == 2004) // relation selected
// in Model Explorer
// or Object Viewer
{
relationship = obj;
}
if (relationship != null)
{
//System.ShowMessageDialog(++dialogId, ‘InfoDialog’, "rel name is " + relationship.Name, 2, 4);

for (fk = 0; fk < relationship.ForeignKeys.Count; fk++)
{
constraint = relationship.ForeignKeys.GetObject(fk).KeyConstraintItem;

//System.ShowMessageDialog(++dialogId, ‘InfoDialog’, constraint.Name, 2, 4);

}

//System.ShowMessageDialog(++dialogId, ‘InfoDialog’, "the fkey index name is " + relationship.ForeignIndex.Name, 2, 4);
name = relationship.ChildEntity.Name + “" + constraint.Name + "” + relationship.ParentEntity.Name + “_” + + “_fk”;
//System.ShowMessageDialog(++dialogId, ‘InfoDialog’, "new rel name is " + name, 2, 4);
relationship.Name = name;
relationship.Caption = name;
}

}

Question:

Relationship name : ChildEntity_ForignKey_ParentEntity_ParentKey_FK

For above relationship name, how to get the ParentKey i.e. the column in the parent table it is referring to?

Please let me know if you need more information.

Regards,

Lalit

Hi Lalit,

Relationship has list of Foreign Keys Relationship.ForeignKeys. You can find items of this list in Referencial Guide (Main Menu - Expert Mode - Referencial Guide) under class name PERForeignKey.

This Item has properties:

  • AttrParent - Source Attribute PERAttribute type
  • AttrChild - Destination Attribute PERAttribute type
  • KeyConstraintItem - Item of Source Key PERKeyConstraintItem type (KeyConstraintItem.Owner is Source Key)
    So if you have relationship with key, that has only one attribute you can get name of parent attribute from relationship:

Relationship.ForeignKeys.GetObject(0).AttrParent.Name

Remember, that key can has more that one attribute.

Hi Lalit,

you can get Parent Attribute from Relationships:

Relationships.ForeignKeys.GetObject(0).AttrParent.Name

or

Relationships.ForeignKeys.GetObject(0).KeyConstraintItem.Attribute.Name

Name of Parent Key:

Relationships.Key.Name


You can find more information in Reference Guide under class names PERForeignKey

Petr

Hi Daril,

Thank you so much for your help. The item properties is exactly what I was looking for.

More specifically, the following answered my question : relationship.ForeignKeys.GetObject(0).AttrChild.Name

Thanks again!

Regards,

Lalit

Hey Daril,

I am unable to mark your reply as an answer. Clicking “Yes” to “Does this answers your question” has no action and page still shows question as “not answered”. Your first reply could be marked as an answer.

Hi Lalit,

Thank you for reporting this, I tried it as well with the same result. I will report it to the site administrator.

Regards,

Lukas