Where does Informix store info on referential constraints?

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. Alanoly J. Andrews

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 Jonathan's answer is absolutely correct. I just want to try to demystify it for you a bit. Basically the column idxname in the sysconstraints record with constrtype 'R' for the appropriate tabid names the index on the child table that supports the foreign key and is the source for the colno's of the key columns. Now in sysreferences lookup the record with the same constrid as the sysconstraints row and join the sysreferences column ptabid back to systables to find the parent table name and the sysreferences column primary holds the constrid of the corresponding primary or unique key constraint. Now you can use primary to query sysconstraints again to find the idxname of the index supporting the referenced table's primary key and use that again in sysindexes to obtain the list of colno's in the key. There that was simple! ;( Jonathan presents the SQL to do this in as few queries as possible. I personally prefer to use program loops and multiple cursors on simpler queries and to look up colnames in a local array loaded from syscolumns before hand. See the code for myschema.ec for examples. Don't feel badly if this and Jonathan's message get you reaching for the ibuprofen. When I first started to flesh out myschema.ec to support constraints I got it wrong three times. Art S. Kagel