FK constraint with colname <> in parent & child table

[sergeyken]

If you ever get lucky to find the answer, please, post it here.
Most of us are rarely faced those specific features, but it may be useful to better know them.

[engh]

I finally found the solution
You have to go through SYSIBM.SYSKEYS to retrieve the information from the index referenced in SYSRELS. IXNAME, for the columns in the order of SYSFOREIGNKEYS.COLSEQ

To make it more meaningful, I created a multi-column FK constraint in the following tables :

Code: Select all

  CREATE TABLE SCH0.T020                                          
      (  T20C01   SMALLINT NOT NULL  PRIMARY KEY                    
       , T20C02   CHAR(8)  NOT NULL                                
       , T20C03   DATE     NOT NULL                                
       , T20C04   INTEGER  NOT NULL                                
       , T20C05   SMALLINT NOT NULL                                
       , UNIQUE (T20C04, T20C05)                                    
      )                                                            
   ;
   CREATE UNIQUE INDEX SCH0.UT020              
       ON SCH0.T020 (T20C04 ASC, T20C05 ASC)    
       USING STOGROUP
   [. . .]
   ;
   CREATE TABLE SCH0.T021                              
      (  T21C01   SMALLINT NOT NULL  PRIMARY KEY      
       , T21C02   INTEGER                              
       , T21C03   SMALLINT                            
       , T21C04   DATE     NOT NULL                    
       , FOREIGN KEY FK21AK20(T21C02, T21C03)          
                 REFERENCES SCH0.T020(T20C04, T20C05)  
                 ON DELETE SET NULL                    
      )                                                
   ;

With the following query (note the addition of the SYSKEYS table) compared to my previous queries :

Code: Select all

   set current schema = 'SYSIBM'                                  
   ;                                                              
   with CT1(SCH1, TAB1) as                                        
       (select 'SCH0', 'T020' from SYSIBM.SYSDUMMY1   union all  
        select 'SCH0', 'T021' from SYSIBM.SYSDUMMY1              
       )                                                          
   select distinct                                                
          substr(RL.CREATOR, 01, 4)     as "Schm"                
        , substr(RL.REFTBNAME, 01, 08)  as "ParentTb"            
        , substr(RL.TBNAME, 01, 08)     as "Child Tb"            
        , substr(RL.RELNAME, 01, 20)    as "Constr"              
        , RL.DELETERULE                 as "OnDel"                
        , RL.ENFORCED                   as "Enf"                  
        , RL.CHECKEXISTINGDATA          as "Chk"                  
        , substr(KY.COLNAME, 01, 08)    as "ParenCol"            
        , KY.COLNO                      as "PColNo"              
        , substr(FK.COLNAME, 01, 08)    as "ChildCol"            
        , FK.COLNO                      as "CColNo"              
   from  SYSRELS  RL                                              
   inner join CT1 CT                                              
      on (RL.CREATOR      = CT.SCH1  and  RL.TBNAME    = CT.TAB1)
      or (RL.REFTBCREATOR = CT.SCH1  and  RL.REFTBNAME = CT.TAB1)
   inner join SYSFOREIGNKEYS FK                                  
      on FK.CREATOR = RL.CREATOR                                  
     and FK.RELNAME = RL.RELNAME                                  
   left  join SYSKEYS KY                                          
      on KY.IXCREATOR=CT.SCH1                                    
     and KY.IXNAME   =RL.IXNAME                                  
     and KY.COLSEQ   =FK.COLSEQ                                  
   order by "ParentTb"                                            
          , "Child Tb"                                            
          , "Constr"                                              
          , "CColNo"                                              
   ;  
 

With the following query:

I finally get the expected result :

Code: Select all

---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------
Schm  ParentTb  Child Tb  Constr                OnDel  Enf  Chk  ParenCol  PColNo  ChildCol  CColNo      
---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+------
SCH0  T020      T021      FK21AK20              N      Y    I    T20C04         4  T21C02         2      
SCH0  T020      T021      FK21AK20              N      Y    I    T20C05         5  T21C03         3      

[sergeyken]

Thank you for this submission!

It's good to know this, for the future.