Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  How can I reconstruct foreign key constraints from system catalogs?

    Posted 21 hours ago

    Hi again, Family.

    At least 10 years ago I posted a question about retrieving information of foreign key constraints.  Of course, we have a method to get this in dbaccess:

    dbaccess <database>
    -> Query-language
    -> Info
    -> (Select table)
    -> cOnstraints (Press letter O)
    ->  Reference
    -> referenceD (Press D)

    This gives me a nice list of all foreign key constraints on other tables that reference the selected table.
    Minor annoyance: A long constraint name will get truncated.

    What system catalogs control these constraints?  I know sysconstraints is involved because I was able to select from sysconstraints where constrname = "<first column in the above list>". And I see that the constrtype == R.  But where do I find on the table being referenced?

    When I asked this over 10 years ago, someone responded that I would need to join with information in some undocumented system catalog and told me which ones.  To my shock, I see that I have not saved the SQL.  Perhaps I posted it in IIUG Software Repository but after 10+ years, where is that?

    HMMM... If I could search this forum for my old postings it would also be helpful.

    Ideas, anyone, please?

    Thanks for advice here.



    ------------------------------
    +-----------------------------------------------------------+
    | I am pleased to report that I had no problems today. |
    | I had only issues, opportunities, challenges and valuable |
    | learning experiences. |
    +------------------------------------------ Jacob S --------+
    ------------------------------


  • 2.  RE: How can I reconstruct foreign key constraints from system catalogs?

    Posted 20 hours ago

    sysreferences is your friend, its primary column pointing to the (PK or Unique) constraint on the referenced table.



    ------------------------------
    Andreas Legner
    Informix Dev
    HCL Software
    ------------------------------

    Original Message


  • 3.  RE: How can I reconstruct foreign key constraints from system catalogs?

    Posted 19 hours ago

    Thanks, Andreas; that is certainly a starting point.  You state:

    sysreferences is your friend, its primary column pointing to the (PK or Unique) constraint on the referenced table

    OK, here are the columns of both catalogs:

    sysconstraints: sysreferences:
    Column name          Type           Column name          Type   
    constrid             serial         constrid             integer
    constrname           varchar(128,0) primary              integer
    owner                char(32)       ptabid               integer
    tabid                integer        updrule              char(1)
    constrtype           char(1)        delrule              char(1)
    idxname              varchar(128,0) matchtype            char(1)
    collation            char(36)       pendant              char(1)

    Thus, making a few guesses, I can up with this query, that seems to correctly match up master and detail tables:

    select tp.tabname pk_tabname, tr.tabname fk_tabname, c.idxname,
           c.constrname, c.constrtype,
           c.constrid, c.tabid detail_tabid, r.ptabid master_tabid,
           r.primary
      from sysconstraints c, systables tr, systables tp, sysreferences r
     where tr.tabid = c.tabid
       and tp.tabid = r.ptabid
     --and r.primary = c.constrid
       and r.constrid = c.constrid
       and c.constrtype = "R"
     order by 1, 2

    Notice: The one line mentioning column primary in the WHERE clause is commented out.  If I include it I get no rows found.  What column it sysreferences.primary supposed to correlate with, if not the constrid of the PK table?

    Now, as I said in opening, this is a start. I see that Art K piped in while I was composing this response. From his solution I can gain an understanding of primary. I think..  And he has the column names accounted for!  But it requires study, which I will give it.

    Thank you much, gentlemen!



    ------------------------------
    +-----------------------------------------------------------+
    | I am pleased to report that I had no problems today. |
    | I had only issues, opportunities, challenges and valuable |
    | learning experiences. |
    +------------------------------------------ Jacob S --------+
    ------------------------------

    Original Message


  • 4.  RE: How can I reconstruct foreign key constraints from system catalogs?

    Posted 19 hours ago

    Jacob: 

    Here is the query that myschema uses. Just substitute the table name in the "AND st.tabname MATCHES" clause instead of '*' or substitute "AND rt.tabname MATCHES '<tabname>' " to filter in the referenced table instead of the table that owns the constraint:

    SELECT st.tabname, st.owner, rt.tabname, rt.owner, sr.primary, sr.ptabid,  
           sr.delrule, sc.constrid, sc.constrname, sc.constrtype, sc.owner, 
           si.idxname, si.tabid, si.part1, si.part2, si.part3,  
           si.part4, si.part5, si.part6, si.part7, si.part8,  
           si.part9, si.part10, si.part11, si.part12, si.part13,  
           si.part14, si.part15, si.part16, rc.tabid, os.state,
           os2.state 
    FROM systables st, sysconstraints sc, 
         sysindexes si, sysreferences sr, 
         systables rt, sysconstraints rc, 
         sysobjstate os, sysobjstate os2 
    WHERE st.tabid = sc.tabid 
      AND st.tabtype != 'Q'
      AND rt.tabid = sr.ptabid 
      AND sc.constrid = sr.constrid 
      AND sc.tabid = si.tabid 
      AND sc.idxname = si.idxname 
      AND sc.constrtype = 'R' 
      AND st.tabname MATCHES '*' 
      AND os.tabid = st.tabid
      AND os.name = sc.constrname
      AND os.objtype = 'C' 
      AND os2.tabid = st.tabid
      AND os2.name = si.idxname
      AND os2.objtype = 'I' 
      AND sr.primary = rc.constrid
    ORDER BY si.tabid, sc.constrid ;



    ------------------------------
    Art S. Kagel, President and Principal Consultant
    ASK Database Management Corp.
    www.askdbmgt.com
    ------------------------------

    Original Message


Related Content