Re: Finding a foreign key to any given table

Mark J Fenbers wrote: > Given a table existing in my database called "AAA", I can determine if a > foreign key exists for "AAA" by looking up the "tabid" in the systables > and finding any "R" constraints on the table by searching in > sysconstraints for that tabid. Then, I can look up the constraint ID in > the sysreferences table to find the ID of the table that is the "target" > of the constraint (table "BBB"). > > (I can even determine what column(s) in "AAA" are bound to a constraint > by looking up the constraint ID in the sysindexes table.) > > But what I cannot seem to figure out is what column(s) in "BBB" are > compared when Informix tests the constraint. Would this be the Primary > Key for "BBB", or not necessarily so? If my software were to output a > message upon encountering error -691, I would want to create a message > that would be explicit about what table has to be updated and which > columns in both "AAA" and "BBB" must match, so that the user knows how > to eliminate this error... > > Any ideas? Dear Mark, We had loosely related discussions before about IDS metadata. I mentioned the code in sqlinfo.ec from SQLCMD, available in the IIUG Software Archive. It has a query for determining the tables and indexes referenced by a given table, or the tables and indexes referencing the given table - INFO REFERENCES BY <tablename> or INFO REFERENCES TO <tablename>. It doesn't deduce the columns (that would make an already ghastly complex query far worse because of the extra 32(!) outer joins that would be needed - ok; you could use 30 outer joins and 2 inner joins), but that's a relatively straightforward exercise - see other queries in the file. You could probably also, or alternatively, use Art Kagel's utils2_ak package to find out how he does the equivalent job. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Hi Mark I developed a 4gl program to generate the all constraints of a given table. It receives the parameters: tablename and database. The result is a file named: constr.database.tablename.sql (in unix systems). If you want this 4gl program, I can send it to you, in your e-mail address. Please, contact me if you want. Best regards. R Ferronato ( roeferr@ig.com.br ) Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<zs3bd.1832$6k2.786@newsread3.news.pas.earthlink.net>... > Mark J Fenbers wrote: > > > Given a table existing in my database called "AAA", I can determine if a > > foreign key exists for "AAA" by looking up the "tabid" in the systables > > and finding any "R" constraints on the table by searching in > > sysconstraints for that tabid. Then, I can look up the constraint ID in > > the sysreferences table to find the ID of the table that is the "target" > > of the constraint (table "BBB"). > > > > (I can even determine what column(s) in "AAA" are bound to a constraint > > by looking up the constraint ID in the sysindexes table.) > > > > But what I cannot seem to figure out is what column(s) in "BBB" are > > compared when Informix tests the constraint. Would this be the Primary > > Key for "BBB", or not necessarily so? If my software were to output a > > message upon encountering error -691, I would want to create a message > > that would be explicit about what table has to be updated and which > > columns in both "AAA" and "BBB" must match, so that the user knows how > > to eliminate this error... > > > > Any ideas? > > Dear Mark, > > We had loosely related discussions before about IDS metadata. I > mentioned the code in sqlinfo.ec from SQLCMD, available in the IIUG > Software Archive. It has a query for determining the tables and > indexes referenced by a given table, or the tables and indexes > referencing the given table - INFO REFERENCES BY <tablename> or > INFO REFERENCES TO <tablename>. It doesn't deduce the columns (that > would make an already ghastly complex query far worse because of the > extra 32(!) outer joins that would be needed - ok; you could use 30 > outer joins and 2 inner joins), but that's a relatively > straightforward exercise - see other queries in the file. > > You could probably also, or alternatively, use Art Kagel's utils2_ak > package to find out how he does the equivalent job.