SQL Developer Data Modeler

Hi Kent,

No, it's not possible to tune FK discovery wizard in that extent. You can try the following script:

Philip

var settings = model.getAppView().getSettings();
//function to get column by matching name from position
//and data type - similar data types can be allowed in preferences - DM 4.1 EA2+
//position is 0 based
function getColumn(table,refCol,position){
    partName = refCol.getName().substring(position);
    cols = table.getElementsCollection().toArray();
    for(var i = 0;i<cols.length;i++){
        col = cols[i];
        if(!col.equals(refCol)){
        cpartName = col.getName().substring(position);
        if(partName.equals(cpartName)){
            if(!settings.isAllowSimilarTypesFK() && col.hasEqulaDataType(refCol)
                ||settings.isAllowSimilarTypesFK() && col.hasSimilarDataType(refCol) ) {
                    return col;   
                }
        }
        }
    }
    return null;
}
tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
table = tables[t];
pk = table.getPK();
if(pk!=null){
    for (var p = 0; p<tables.length;p++){
      ctab = tables[p];
      list = new java.util.ArrayList(); 
      pk_cols = pk.getColumns();
      for(var k=0;k<pk_cols.length;k++){   
      //position is 0 based so 4th character is at position 3     
        col = getColumn(ctab,pk_cols[k],3);
        if(col!=null){
            list.add(col);
        }
      }
      //create FK if the number of columns is the same
      if(list.size()==pk_cols.length){
          ctab.addForeignKey(pk,list);
      }
    }
}
}

Thanks. Will give this a try. What do you mean - "similar data types can be allowed in preferences - DM 4.1 EA2+ "?

similar data types for FK columns - number(7) and Number(10) are similar types they are not exact as it's required for PK-FK columns matching in previous releases. It appears it's not included in 4.1 EA2. Sorry, I'll change the script tomorrow to not use

here is the new script that will work in 4.1.873

var settings = model.getAppView().getSettings();
//function to get column by matching name from position
//position is 0 based
function getColumn(table,refCol,position){
    partName = refCol.getName().substring(position);
    cols = table.getElementsCollection().toArray();
    for(var i = 0;i<cols.length;i++){
        col = cols[i];
        if(!col.equals(refCol)){
         cpartName = col.getName().substring(position);
         if(partName.equals(cpartName) && col.hasEqulaDataType(refCol) ){
             return col; 
         }
        }
    }
    return null;
}
tables = model.getTableSet().toArray();
for (var t = 0; t<tables.length;t++){
 table = tables[t];
 pk = table.getPK();
 if(pk!=null){
     for (var p = 0; p<tables.length;p++){
      ctab = tables[p];
      list = new java.util.ArrayList();  
      pk_cols = pk.getColumns();
      for(var k=0;k<pk_cols.length;k++){          
         col = getColumn(ctab,pk_cols[k],3);
         if(col!=null){
             list.add(col);
         }
      }
      //create FK if the number of columns is the same
      if(list.size()==pk_cols.length){
          ctab.addForeignKey(pk,list);
      }
     }
 }
}

It worked, mostly.

Got an error on these lines:

if(!settings.isAllowSimilarTypesFK() && col.hasEqulaDataType(refCol) 

                    ||settings.isAllowSimilarTypesFK() && col.hasSimilarDataType(refCol) )

It said isAllowSimilarTypesFK was not a valid function (no such function).

The other issue, if the FK is already there, I don't want another. I had already built a few by hand so the script created dups. So an FK from Tab1 to Tab2 is already there, that should be skipped.

Thanks.

Hi Kent,

in DM 4.1.1.888 you don't need script to get the similar result.

Cases covered:

1) prefix is in table abbreviation, then FK column template should be

{table abbr}SUBSTR(4,30,FRONT,{ref column})

2) first 3 (or another number) characters from table name are used as prefix for columns then template is:

SUBSTR(1,3,FRONT,{table})SUBSTR(4,30,FRONT,{ref column})

I say similar result because the script and wizard work in different ways:

- the wizard generates the name based on template and search for column with such name (and data type)

- the script is doing partial matching of parent and child columns

Philip

Cool! Thanks for the update and the template code.