5 Replies Latest reply: Oct 11, 2012 5:09 AM by Philip Stoyanov-Oracle RSS

    FK`s associated with PK/UK

    904770
      Hi.

      What method return a list of FK associated with PK/UK in T-script?
      I trouble to find the properly metod Table/Index classes.
        • 2. Re: FK`s associated with PK/UK
          904770
          No.
          I want to kill by T-script all Primary/Unique keys. However i need leave the key if exists relation associated with it.
          Brute force search solve the problem, but maybe exist simplest path?
          • 3. Re: FK`s associated with PK/UK
            marcusafs
            You can use this to create SQL to accomplish your task.
            SELECT *
            FROM   all_constraints con
            WHERE      NOT EXISTS
                             (SELECT NULL
                              FROM   all_constraints coni
                              WHERE      con.owner = coni.owner
                                     AND con.table_name = coni.table_name
                                     AND coni.r_constraint_name = con.constraint_name
                                     AND con.constraint_type = 'R')
                   AND con.constraint_type IN ('P', 'U')
            ORDER BY con.owner,
                     con.table_name;
            • 4. Re: FK`s associated with PK/UK
              904770
              Thanks a lot.
              I know how i can find contraints on DB site.
              I ask how i can do it on Modeler site with T-script?

              OK, lets try this: open the Table properties-> "Primary key" or "Unique Constraints" -> Key Properties -> "Impact Analysis" -> choose "FKs and child tables" tree node...
              And I see nothing if key has no associated relations or list what i need if child table FK uses this key.

              Is there some function returning list of FK associated on PK/UK or described functionality used the full model search?

              Edited by: alexN on 11.10.2012 0:18
              • 5. Re: FK`s associated with PK/UK
                Philip Stoyanov-Oracle
                Hi Alex,

                you can write such special function. Here is the code:
                //define function
                function hasAssociatedFK(key,fkeys){
                 for (var i = 0; i < fkeys.length; i++) {
                  fk = fkeys;
                if(fk.getRemoteIndex()==key){
                return true;
                }
                }
                return false;
                }
                //and use it later
                fkeys = model.getFKIndexAssociationSet().toArray();
                tables = model.getTableSet().toArray();
                for (var i = 0; i < tables.length; i++) {
                table = tables[i];
                keys = table.getKeySet().toArray();
                for(var k = 0;k < keys.length; k++){
                     key = keys[k];
                if((key.isPK() || key.isUnique()) && !hasAssociatedFK(key,fkeys)){
                     //do what you want to do
                     keyName = key.getName();
                }
                }
                }
                Edited by: Philip Stoyanov on Oct 11, 2012 3:08 AM