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

On Wed, 8 Jul 1998, Jonathan Leffler wrote: > 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). Here's a query which determines all the information except the column names. SELECT c1.constrname refng_constr_name, c1.owner refng_constr_owner, c1.idxname refng_index_name, t2.tabid refng_table_tabid, t2.owner refng_table_owner, t2.tabname refng_table_name, c2.constrname refed_constr_name, c2.owner refed_constr_owner, c2.idxname refed_index_name, t1.tabid refed_table_tabid, t1.owner refed_table_owner, t1.tabname refed_table_name FROM 'informix'.SysReferences r, 'informix'.SysConstraints c1, 'informix'.SysConstraints c2, 'informix'.SysTables t1, 'informix'.SysTables t2 WHERE c1.constrtype = 'R' AND c1.tabid = t2.tabid AND c1.constrid = r.constrid AND r.ptabid = t1.tabid AND r.primary = c2.constrid The "c1.constrtype = 'R'" condition is redundant; only constraints where that is the case will meet all the criteria. One interesting sidelight; there's a unique index on the combination of index name, index owner and tabid in SysIndexes, but even in a MODE ANSI database, you cannot have two indexes with the same name but different owners (eg 'user1'.index1 and 'user2'.index2). But this constraint is not enforced by the index; there is extra code enforcing it... One nasty wrinkle on the determination of column names for an entry in SysIndexes -- descending keys have a negative number for the partN column; the absolute value of the column number is used to reference the SysColumns table. Yours, Jonathan Leffler (jleffler@informix.com) #include <witticism.h> Guardian of DBD::Informix -- see http://www.perl.com/CPAN --------------------------------------------------------------------------- Details: Given the schema: CREATE TABLE MASTER ( Key SERIAL NOT NULL , data CHAR(30) NOT NULL , PRIMARY KEY (Key) CONSTRAINT pk_master ); CREATE TABLE detail ( KEY INTEGER NOT NULL REFERENCES master, moredata CHAR(30) NOT NULL ); CREATE TABLE m2 ( c1 SERIAL NOT NULL , PRIMARY KEY (c1) CONSTRAINT pk_m2 ); CREATE TABLE d2 ( r1 INTEGER NOT NULL REFERENCES m2 CONSTRAINT fk_d2 ); The output from the query was: refng_constr_name|refng_constr_owner|refng_index_name|refng_table_tabid|refng_table_owner|refng_table_name|refed_constr_name|refed_constr_owner|refed_index_name|refed_table_tabid|refed_table_owner|refed_table_name r101_4|jleffler| 101_4|101|jleffler|detail|pk_master|jleffler| 100_1|100|jleffler|master fk_d2|jleffler| 103_9|103|jleffler|d2|pk_m2|jleffler| 102_7|102|jleffler|m2