FK constraint with colname <> in parent & child table

[engh]

Hello

I would like to be able to analyze foreign key constraints by query on the relational catalog
I am faced with a difficulty in the case where the referenced column does not have the same name as the referencing column

For example, let's say the following DDL

CREATE TABLE SCH1.TAB1
( B1IDEN SMALLINT NOT NULL PRIMARY KEY
, FKIDEN SMALLINT DEFAULT NULL
, B1DATE DATE NOT NULL
, FOREIGN KEY FK01PK00(FKIDEN)
REFERENCES SCH1.TAB0(B0IDEN)
ON DELETE SET NULL
)
When I look at the relational catalog, tables SYSIBM.SYSRELS and SYSIBM.SYSFOREIGNKEYS, I find
the name of the child table (TAB1)
the name of the referenced table (TAB0)
the name of the constraint (FK01PK00)
the name of the column in the child table (FKIDEN)
but not the name of the column in the parent table(B0IDEN)

Where can i find this name in the catalog tables (SYSIBM.SYS*)
I know how to find this information with some tools (BMC, Platinum...), but I would like to find it in the relational catalog

[sergeyken]

Try to start from reading the IBM documentation, with examples -
Retrieving catalog information about foreign keys

[engh]

I found nothing in the ibm documentation
And I consulted all the SYSIBM.SYS% tables in the catalog, but without success

[sergeyken]

To retrieve the constraint name, column names, and parent table names for every relationship in which the project table is a dependent, execute:

Code: Select all

SELECT A.CREATOR, A.TBNAME, A.RELNAME, B.COLNAME, B.COLSEQ,
       A.REFTBCREATOR, A.REFTBNAME
  FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B
  WHERE A.CREATOR = 'DSN8C10'
  AND B.CREATOR = 'DSN8C10'
  AND A.TBNAME = 'PROJ'
  AND B.TBNAME = 'PROJ'
  AND A.RELNAME = B.RELNAME
    ORDER BY A.RELNAME, B.COLSEQ;

To find information about the foreign keys of tables to which the project table is a parent:

Code: Select all

SELECT A.RELNAME, A.CREATOR, A.TBNAME, B.COLNAME, B.COLNO
  FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B
  WHERE A.REFTBCREATOR = 'DSN8C10'
  AND A.REFTBNAME = 'PROJ'
  AND A.RELNAME = B.RELNAME
    ORDER BY A.RELNAME, B.COLNO;

[engh]

Thanks but this query does not return the name of the column in the referenced table, only the name in the referencing table.

So it does not work for the expressed need

I remind you that the problem only arises if the name of the column is different in the two tables

Here is my current query, which works fine, except for the case where the column name differs

Code: Select all

  with CT1(SCH1, TAB1) as                                
       (select 'SCH1', 'TAB1' from SYSIBM.SYSDUMMY1   union all
        select 'SCH1', 'TAB0' from SYSIBM.SYSDUMMY1
       )                                          
   select distinct                                                            
          substr(RL.CREATOR, 01, 4)     as "Schm"                              
        , substr(RL.REFTBNAME, 01, 08)  as "Parent"                            
        , substr(RL.TBNAME, 01, 08)     as "Enfant"                            
        , substr(RL.RELNAME, 01, 20)    as "Contrainte"                        
        , RL.DELETERULE                 as "OnDel"                            
        , RL.ENFORCED                   as "For"                              
        , RL.CHECKEXISTINGDATA          as "Chk"                              
        , FK.COLNO                      as "ColNo"                            
        , substr(FK.COLNAME, 01, 08)    as "ColName"                          
   from SYSIBM.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 SYSIBM.SYSFOREIGNKEYS FK                                        
      on FK.CREATOR = RL.CREATOR                                              
     and FK.RELNAME = RL.RELNAME                                              
   order by "Parent"                                                          
          , "Enfant"                                                          
          , "Contrainte"                                                      
          , "ColNo"                                                            
   ;                

Note the "JOIN" operator rather than coding join in WHERE restriction
In compliance with the SQL 1992 standard, clearer and sometimes more efficient

[sergeyken]

Need to look deeper into this issue, and to run some tests to analyze the results.
Will try to do this next week, time permitting.

[sergeyken]

Try to find 3 required fields:
REFTBCREATOR from SYSIBM.SYSRELS
REFTBNAME from SYSIBM.SYSRELS
COLSEQ from SYSIBM.SYSFOREGNKEYS

Use those as the search key in SYSIBM.SYSCOLUMNS, to extract (column)NAME field by matching the fields:
TBCREATOR
TBNAME
COLNO

[engh]

SYSIBM.SYSFOREGNKEYS.COLSEQ is not the COLSEQ of the related table, but (extract from IBM documentation) the numeric place of the column in the foreign key.

For sample, with this two tables

Code: Select all

   CREATE TABLE SCH0.T020                              
      (  T20C01   SMALLINT NOT NULL  PRIMARY KEY        
       , T20C02   SMALLINT NOT NULL                    
       , T20C03   DATE     NOT NULL                    
       , T20C04   CHAR(8)  NOT NULL  UNIQUE            
       , T20C05   INTEGER  NOT NULL                    
      )                
   ;  
   CREATE TABLE SCH0.T021                          
      (  T21C01   SMALLINT NOT NULL  PRIMARY KEY    
       , T21C02   SMALLINT NOT NULL                
       , T21C03   CHAR(8)  NOT NULL                
       , T21C05   INTEGER  NOT NULL                
       , FOREIGN KEY FK21AK20(T21C03)              
                 REFERENCES DD0Q.T020(T20C04)      
                 ON DELETE CASCADE                  
      )                            
   ;

The content of SYSIBM.SYSFOREIGNKEYS.COLSEQ is 1 and not 4, so doing a join with SYSCOLUMNS does not work

[sergeyken]

Try
COLNO from SYSIBM.SYSFOREGNKEYS
in place of
COLSEQ from SYSIBM.SYSFOREGNKEYS

One of them must be the needed reference, I guess so.

[engh]

SYSFOREIGNKEYS.COLSEQ=1 and SYSFOREIGNKEYS.COLNO = 3, and 3 is the colnum in the child table, not the colnum in the referenced table : should be 4 in my last sample...

Be sure that I searched a lot, not only in the official IBM documentation, but also by consulting the catalog tables before opening this thread.

The information I am looking for, if it exists, is definitely not stored in either the SYSFOREIGNKEYS or the SYSRELS table