Re: ?? Getting info on foreign keys from sys*

John Mullee wrote: > How can I retrieve, using something like the following query, > a list of tables which reference a table 'X' via foreign keys? > > select T.tabname, C.constrtype, C.constrname, > R.constrid, I.idxname > from sysconstraints C, sysreferences R, > sysindices I, systables T SysIndexes? > where T.tabid = I.tabid > and T.tabid = C.tabid > and R.constrid = C.constrid > and I.idxname = C.idxname > and T.tabname='X' > order by 1, 2, 3, 4, 5; > > this returns: > > popul R r331_1750 1750 331_1750 > popul R r331_1751 1751 331_1751 > popul R r331_1752 1752 331_1752 > popul R r331_1753 1753 331_1753 > popul R r331_1754 1754 331_1754 > > .. but tells me nothing about the fields which are referred from, > or the tables to which they refer.... Well, the index names can be used to determine the columns (but it's hard, because it is a 15-way outer join if you do it properly -- yes, there can be 16 columns in an index, but the first column is always defined and therefore can be determined with a regular inner join). With an FK constraint, there's a non-unique index on the referencing table and a unique index on the referenced table, so that gives you all the column info you might need. Doesn't it? I don't have the manuals immediately at hand to give a more complete answer, which is also partly laziness because typing in two sets of SysIndexes -> SysColumns joins is not a pleasant idea, and because there should be two references to SysTables, and two references to SysIndexes (ignoring the multi-way joins to SysColumns). -- Jonathan Leffler (jleffler@informix.com, jleffler@earthlink.net) Guardian of DBD::Informix -- see http://www.perl.com/CPAN #include <disclaimer.h>