RE: Where does Informix store info on referential constraints?

Thanks, Jonathan, for that detailed answer and the sql. I've not yet incorporated it into my programme, but I now see how it can be done. And thanks to Art, too, for his subsequent elucidation. I haven't looked into "myschema.ec". But since Jonathan has provided the full sql, I might go along with a "cut and paste" of its relevant portions. Alanoly J. Andrews > -----Original Message----- > From: Jonathan Leffler [SMTP:jleffler@informix.com] > Sent: Tuesday, February 23, 1999 4:48 PM > To: Alanoly Andrews > Cc: 'informix-list@iiug.org' > Subject: 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)@@NL@