Geek Answers Handbook

How to get these restrictions on behalf of Informix?

When programming a large transaction (many inserts, deletions, updates) and, thus, violation of the restriction in Informix (v10, but should be applied to other versions). I get a not-so-useful message, for example, that I violated the r190_710 restriction. How can I find out which tables (tables) and key (s) are covered by a certain restriction, I only know the name?

+4
source share
7 answers

Tony Andrews suggested (pointing to another endpoint for the URL):

From the Informix SQL Reference: Help , you should look at the SYSCONSTRAINTS and SYSINDICES system catalog tables.

The Informix system catalog is described in this guide.

The SysConstraints table is the starting point for constraint analysis, of course; You will find the name of the constraint in this table, and from there you can find out other details.

However, you should also look at other tables, not just (or even directly) SysIndices.

For example, I have many NOT NULL restrictions on tables in my database. For them, the type of restriction is "N", and there is no need to look for additional information elsewhere.

The constraint type "P" indicates the primary key; which will require more analysis through the presentation of SysIndexes or SysIndices. Similarly, the constraint type “U” indicates a unique constraint and requires additional information from the SysIndexes view or the SysIndices table.

Constraint type “C” indicates a validation constraint; the text (and binary compiled form) of the constraint is found in the SysChecks table (with types “T” and “B” for data, the data is more or less encoded using Base-64, but without the addition of “=” at the end and using different characters for 62 and 63).

Finally, the constraint type “R” indicates a referential integrity constraint. You use the SysReferences table to find out which table the links are listed in, and you use SysIndexes or SysIndices to determine which indexes in the referenced and referenced tables are used, and from this you can find the corresponding columns. It can get pretty hairy!

+6
source

Columns in a table with a restriction on them

SELECT a.tabname, b.constrname, d.colname FROM systables a, sysconstraints b, sysindexes c, syscolumns d WHERE a.tabname = 'your_table_name_here' AND b.tabid = a.tabid AND c.idxname = b.idxname AND d.tabid = a.tabid AND ( d.colno = c.part1 or d.colno = c.part2 or d.colno = c.part3 or d.colno = c.part4 or d.colno = c.part5 or d.colno = c.part6 or d.colno = c.part7 or d.colno = c.part8 or d.colno = c.part9 or d.colno = c.part10 or d.colno = c.part11 or d.colno = c.part12 or d.colno = c.part13 or d.colno = c.part14 or d.colno = c.part15 or d.colno = c.part16 ) ORDER BY a.tabname, b.constrname, d.colname
+3
source

From the Informix Guide to SQL: Reference , you should look at the SYSCONSTRAINTS and SYSINDICES system catalog tables.

0
source

From surfing on www.iiug.org (Informix International User Group), I have found a not-so-easy solution.

(1) Get the reference constraint data from the constraint name (you can get all the constraints for the table by replacing "AND sc.constrname =?" With "AND st.tabname MATCHES?"). This operator selects more fields than necessary here, because they may be interesting in other situations.

 SELECT si.part1, si.part2, si.part3, si.part4, si.part5, si.part6, si.part7, si.part8, si.part9, si.part10, si.part11, si.part12, si.part13, si.part14, si.part15, si.part16, st.tabname, rt.tabname as reftable, sr.primary as primconstr, sr.delrule, sc.constrid, sc.constrname, sc.constrtype, si.idxname, si.tabid as tabid, rc.tabid as rtabid FROM 'informix'.systables st, 'informix'.sysconstraints sc, 'informix'.sysindexes si, 'informix'.sysreferences sr, 'informix'.systables rt, 'informix'.sysconstraints rc WHERE st.tabid = sc.tabid AND st.tabtype != 'Q' AND st.tabname NOT MATCHES 'cdr_deltab_[0-9][0-9][0-9][0-9][0-9][0-9]*' AND rt.tabid = sr.ptabid AND rc.tabid = sr.ptabid AND sc.constrid = sr.constrid AND sc.tabid = si.tabid AND sc.idxname = si.idxname AND sc.constrtype = 'R' AND sc.constrname = ? AND sr.primary = rc.constrid ORDER BY si.tabid, sc.constrname

(2) Use part1-part16 to determine which column is affected by the constraint: the [n] part, which contains a value other than 0, contains the column number of the column used. Use (3) to find the column name.

If constrtype is "R" (link), use the following statement to search for parts of the link table:

 SELECT part1, part2, part3, part4, part5, part6, part7, part8, part9, part10, part11, part12, part13, part14, part15, part16 FROM 'informix'.sysindexes si, 'informix'.sysconstraints sc WHERE si.tabid = sc.tabid AND si.idxname = sc.idxname AND sc.constrid = ? -- primconstr from (1)

(3) tabid and rtabid (for constraint references) from (1) can now be used to get table columns:

 SELECT colno, colname FROM 'informix'.syscolumns WHERE tabid = ? -- tabid(for referenced) or rtabid(for referencing) from (1) AND colno = ? -- via parts from (1) and (2) ORDER BY colno

(4) If constrtype is "C", then get the control information as follows:

 SELECT type, seqno, checktext FROM 'informix'.syschecks WHERE constrid = ? -- constrid from (1)

Pretty hairy really

0
source

to get the table affected by the restriction "r190_710":

 select TABNAME from SYSTABLES where TABID IN (select TABID from sysconstraints where CONSTRID IN (select CONSTRID from sysreferences where PTABID IN (select TABID from sysconstraints where CONSTRNAME= "r190_710" ) ) );
0
source

If your constraint is named constraint_c6 , here's how to reset its definition (well, sorting, you still need to concatenate strings, since they will be separated by spaces):

 OUTPUT TO '/tmp/constraint_c6.sql' WITHOUT HEADINGS SELECT ch.checktext FROM syschecks ch, sysconstraints co WHERE ch.constrid = co.constrid AND ch.type = 'T' -- text lines only AND co.constrname = 'constraint_c6' ORDER BY ch.seqno;
0
source

I use the following query to get more information about the different types of restrictions. This is based on some spelunking in the system tables and a few explanations about the system catalog.

sysconstraints.constrtype indicates the type of restriction:

  • P = Primary Key
  • U = unique key / alternate key
  • N = Not null
  • C = Check
  • R = link / foreign key
 select tab.tabname, constr.*, chk.*, c1.colname col1, c2.colname col2, c3.colname col3, c4.colname col4, c5.colname col5 from sysconstraints constr join systables tab on tab.tabid = constr.tabid left outer join syschecks chk on chk.constrid = constr.constrid and chk.type = 'T' left outer join sysindexes i on i.idxname = constr.idxname left outer join syscolumns c1 on c1.tabid = tab.tabid and c1.colno = abs(i.part1) left outer join syscolumns c2 on c2.tabid = tab.tabid and c2.colno = abs(i.part2) left outer join syscolumns c3 on c3.tabid = tab.tabid and c3.colno = abs(i.part3) left outer join syscolumns c4 on c4.tabid = tab.tabid and c4.colno = abs(i.part4) left outer join syscolumns c5 on c5.tabid = tab.tabid and c5.colno = abs(i.part5) where constr.constrname = 'your constraint name'
0
source

More articles:


All Articles