How can I reconstruct foreign key constraints from system catalogs?

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 --------+ ------------------------------

sysreferences is your friend, its primary column pointing to the (PK or Unique) constraint on the referenced table. ------------------------------ Andreas Legner Informix Dev HCL Software ------------------------------

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 ------------------------------

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: 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 --------+ ------------------------------