Re: Where does Informix store info on referential constraints?

On Tue, 23 Feb 1999, Alanoly Andrews wrote: >A question on the system tables: > >Suppose I have a table created as: > create table tab1 > (col 1 char, > ........ > );>and another table as: > create table tab2 > (col1 char, > col2 char, > ......., > foreign key col2 references tab1(col1))> >The question is: where does Informix store the information that col2 >of tab2 references col1 of tab1? Using systables, sysconstraints and >sysreferences I can get as far as knowing that tab2 references tab1. >But where is the column dependency found? There is a table called >syscoldepend; but that does not seem to have referential constraints >stored. Welcome to the Byzantine world of the Informix system catalogue. The answer is that the information is buried in SysIndexes and SysColumns. The SysReferences table is the glue between two SysConstraints entries which identify the indexes on the two tables which are paired. Given the index names, you can determine the equivalence between the columns in the index. Here is some SQL which identifies the referencing tables, constraints and indexes, and the corresponding referenced tables, constraints and indexes in a given database. SELECT C1.constrname refng_constr_name, C1.owner refng_constr_owner, C1.idxname refng_index_name, T1.tabid refng_table_tabid, T1.owner refng_table_owner, T1.tabname refng_table_name, C2.constrname refed_constr_name, C2.owner refed_constr_owner, C2.idxname refed_index_name, T2.tabid refed_table_tabid, T2.owner refed_table_owner, T2.tabname refed_table_name FROM 'informix'.SysReferences R, 'informix'.SysConstraints C1, 'informix'.SysConstraints C2, 'informix'.SysTables T2, 'informix'.SysTables T1 WHERE C1.constrtype = 'R' AND C1.tabid = T1.tabid AND C1.constrid = R.constrid AND R.ptabid = T2.tabid AND R.primary = C2.constrid With the index names (refng_index_name and refed_index_name) in hand, you can then interrogate SysIndexes and SysColumns to find the matching columns. Of course, that isn't easy either -- SysIndexes is a pig to handle (can you say 15-way OUTER JOIN for an OnLine table, or a 16-way UNION?). Yours, Jonathan Leffler (jleffler@informix.com) #include <wish/I/was/skiing.h> Guardian of DBD::Informix v0.60 (v0.61_02) -- http://www.perl.com/CPAN Informix IDN for D4GL & Linux -- http://www.informix.com/idn PS: OK, here are two possible queries on SysIndexes for determining the column names, etc... -- 15-way OUTER JOIN; loses ascending/descending information SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, c01.colname col01, c02.colname col02, c03.colname col03, c04.colname col04, c05.colname col05, c06.colname col06, c07.colname col07, c08.colname col08, c09.colname col09, c10.colname col10, c11.colname col11, c12.colname col12, c13.colname col13, c14.colname col14, c15.colname col15, c16.colname col16 FROM "informix".SysIndexes t0, "informix".SysColumns c01, OUTER "informix".SysColumns c02, OUTER "informix".SysColumns c03, OUTER "informix".SysColumns c04, OUTER "informix".SysColumns c05, OUTER "informix".SysColumns c06, OUTER "informix".SysColumns c07, OUTER "informix".SysColumns c08, OUTER "informix".SysColumns c09, OUTER "informix".SysColumns c10, OUTER "informix".SysColumns c11, OUTER "informix".SysColumns c12, OUTER "informix".SysColumns c13, OUTER "informix".SysColumns c14, OUTER "informix".SysColumns c15, OUTER "informix".SysColumns c16 WHERE t0.tabid = 1 AND ABS(t0.part1) = c01.colno AND t0.tabid = c01.tabid AND ABS(t0.part2) = c02.colno AND t0.tabid = c02.tabid AND ABS(t0.part3) = c03.colno AND t0.tabid = c03.tabid AND ABS(t0.part4) = c04.colno AND t0.tabid = c04.tabid AND ABS(t0.part5) = c05.colno AND t0.tabid = c05.tabid AND ABS(t0.part6) = c06.colno AND t0.tabid = c06.tabid AND ABS(t0.part7) = c07.colno AND t0.tabid = c07.tabid AND ABS(t0.part8) = c08.colno AND t0.tabid = c08.tabid AND ABS(t0.part9) = c09.colno AND t0.tabid = c09.tabid AND ABS(t0.part10) = c10.colno AND t0.tabid = c10.tabid AND ABS(t0.part11) = c11.colno AND t0.tabid = c11.tabid AND ABS(t0.part12) = c12.colno AND t0.tabid = c12.tabid AND ABS(t0.part13) = c13.colno AND t0.tabid = c13.tabid AND ABS(t0.part14) = c14.colno AND t0.tabid = c14.tabid AND ABS(t0.part15) = c15.colno AND t0.tabid = c15.tabid AND ABS(t0.part16) = c16.colno AND t0.tabid = c16.tabid; -- 16-way UNION; preserves ascending/descending information CREATE TEMP TABLE _sqlcmd_idxdir (i INTEGER, d CHAR(1)); INSERT INTO _sqlcmd_idxdir VALUES(-1, 'D'); INSERT INTO _sqlcmd_idxdir VALUES(+1, 'A'); SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, 1 idxcolno, t0.part1 tabcolno, t1.colname, t2.d direction FROM "informix".SysIndexes t0, "informix".SysColumns t1, _sqlcmd_idxdir t2 WHERE t0.tabid = 1 AND ABS(t0.part1) = t1.colno AND t0.tabid = t1.tabid AND t2.i = t1.colno / ABS(t1.colno) UNION SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, 2 idxcolno, t0.part2 tabcolno, t1.colname, t2.d direction FROM "informix".SysIndexes t0, OUTER ("informix".SysColumns t1, _sqlcmd_idxdir t2) WHERE t0.tabid = 1 AND t0.part2 != 0 AND ABS(t0.part2) = t1.colno AND t0.tabid = t1.tabid AND t2.i = t1.colno / ABS(t1.colno) UNION SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, 3 idxcolno, t0.part3 tabcolno, t1.colname, t2.d direction FROM "informix".SysIndexes t0, OUTER ("informix".SysColumns t1, _sqlcmd_idxdir t2) WHERE t0.tabid = 1 AND t0.part3 != 0 AND ABS(t0.part3) = t1.colno AND t0.tabid = t1.tabid AND t2.i = t1.colno / ABS(t1.colno) UNION SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, 4 idxcolno, t0.part4 tabcolno, t1.colname, t2.d direction FROM "informix".SysIndexes t0, OUTER ("informix".SysColumns t1, _sqlcmd_idxdir t2) WHERE t0.tabid = 1 AND t0.part4 != 0 AND ABS(t0.part4) = t1.colno AND t0.tabid = t1.tabid AND t2.i = t1.colno / ABS(t1.colno) UNION SELECT t0.tabid, t0.idxtype, t0.owner, t0.idxname, 5 idxcolno, t0.part5 tabcolno, t1.colname, t2.d direction FROM "informix".SysIndexes t0, OUTER ("informix".SysColumns t1, _sqlcmd_idxdir t2) WHERE t0.tabid = 1 AND t0.part5 != 0 AND ABS(t0.part5) = t1.colno AND t0.tabid = t1.tabid AND t2.i = t1.colno / ABS(t1.colno) UNION SELECT