database 如何从 Informix 中的名称获取约束详细信息？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/320045/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I get constraint details from the name in Informix?
提问by user39039
When programming a large transaction (lots of inserts, deletes, updates) and thereby violating a constraint in Informix (v10, but should apply to other versions too) I get a not very helpful message saying, for example, I violated constraint r190_710. How can I find out which table(s) and key(s) are covered by a certain constraint I know only the name of?
在对大型事务（大量插入、删除、更新）进行编程并因此违反 Informix（v10，但也应适用于其他版本）中的约束时，我收到一条不太有用的消息，例如，我违反了约束 r190_710。如何找出我只知道名称的某个约束覆盖了哪些表和键？
回答by Jonathan Leffler
Tony Andrews suggested (pointing to a different end-point for the URL):
Tony Andrews 建议（指向 URL 的不同端点）：
From Informix Guide to SQL: Referenceit appears you should look at the system catalog tables SYSCONSTRAINTS and SYSINDICES.
从Informix Guide to SQL: Reference看来，您应该查看系统目录表 SYSCONSTRAINTS 和 SYSINDICES。
The Informix system catalog is described in that manual.
该手册中描述了 Informix 系统目录。
The SysConstraints table is the starting point for analyzing a constraint, most certainly; you find the constraint name in that table, and from there you can find out the other details.
SysConstraints 表是分析约束的起点，当然；您可以在该表中找到约束名称，然后您可以从那里找到其他详细信息。
However, you also have to look at other tables, and not just (or even directly) SysIndices.
但是，您还必须查看其他表，而不仅仅是（甚至直接）SysIndices。
For example, I have a lot of NOT NULL constraints on the tables in my database. For those, the constraint type is 'N' and there is no need to look elsewhere for more information.
例如，我的数据库中的表有很多 NOT NULL 约束。对于那些，约束类型为“N”，无需在别处查找更多信息。
A constraint type of 'P' indicates a primary key; that would need more analysis via the SysIndexes view or SysIndices table. Similarly, a constraint type of 'U' indicates a unique constraint and needs extra information from the SysIndexes view or SysIndices table.
'P' 的约束类型表示主键；这将需要通过 SysIndexes 视图或 SysIndices 表进行更多分析。类似地，“U”约束类型表示唯一约束，并且需要来自 SysIndexes 视图或 SysIndices 表的额外信息。
A constraint type of 'C' indicates a check constraint; the text (and binary compiled form) of the constraint is found in the SysChecks table (with types 'T' and 'B' for the data; the data is more or less encoded with Base-64, though without the '=' padding at the end and using different characters for 62 and 63).
'C' 的约束类型表示检查约束；约束的文本（和二进制编译形式）可在 SysChecks 表中找到（数据类型为“T”和“B”；数据或多或少使用 Base-64 编码，但没有“=”填充最后并为 62 和 63 使用不同的字符）。
Finally, a constraint type of 'R' indicates a referential integrity constraint. You use the SysReferences table to find out which table is referenced, and you use SysIndexes or SysIndices to establish which indexes on the referencing and referenced tables are used, and from that you can discover the relevant columns. This can get quite hairy!
最后，“R”的约束类型表示参照完整性约束。您可以使用 SysReferences 表找出被引用的表，并使用 SysIndexes 或 SysIndices 来确定引用表和被引用表上的哪些索引被使用，从中可以发现相关列。这会变得很毛茸茸！
回答by cab
Columns in a table with a constraint 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
回答by Otherside
I have been using the following query to get more information about the different types of constraints. It's based on some spelunking in the system tables and several explanations about the system catalog.
我一直在使用以下查询来获取有关不同类型约束的更多信息。它基于对系统表的一些探索和关于系统目录的一些解释。
sysconstraints.constrtype indicates the type of the constraint:
sysconstraints.constrtype 表示约束的类型：
- P = Primary key
- U = Unique key / Alternate key
- N = Not null
- C = Check
- R = Reference / Foreign key
- P = 主键
- U = 唯一键/备用键
- N = 非空
- C = 检查
- R = 参考/外键
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'
回答by Santiago Taba
to get the table affected by the constraint "r190_710":
获取受约束“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" )
)
);
回答by ekarak
If your constraint is named constraint_c6, here's how to dump its definition (well sort-of, you still need to concatenate the rows, as they'll be separated by whitespace):
如果您的约束名为constraint_c6，这里是如何转储其定义（好吧，您仍然需要连接行，因为它们将被空格分隔）：
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;
回答by Tony Andrews
From Informix Guide to SQL: Referenceit appears you should look at the system catalog tables SYSCONSTRAINTS and SYSINDICES.
从Informix Guide to SQL: Reference看来，您应该查看系统目录表 SYSCONSTRAINTS 和 SYSINDICES。
回答by user39039
From surfing at www.iiug.org (International Informix Users Group) i found the not-so-easy solution.
通过浏览 www.iiug.org（国际 Informix 用户组），我找到了一个不太容易的解决方案。
(1) Get referential constraint data from the constraint name (you can get all constraints for a table by replacing "AND sc.constrname = ?" by "AND st.tabname MATCHES ?"). This statement selects some more fields than necessary here because they might be interesting in other situations.
(1) 从约束名称中获取引用约束数据（您可以通过将“AND sc.constrname = ?”替换为“AND st.tabname MATCHES ?”来获取表的所有约束）。此语句在这里选择了一些不必要的字段，因为它们在其他情况下可能很有趣。
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 the part1-part16 to determine which column is affected by the constraint: the part[n] containing a value different from 0 contains the column number of the used column. Use (3) to find the name of the column.
(2)用part1-part16判断哪一列受约束：part[n]包含的值不为0的包含所用列的列号。使用 (3) 查找列的名称。
If constrtype is 'R' (referencing) use the following statement to find the parts of the referencing table:
如果 constrtype 是 'R'（引用），请使用以下语句来查找引用表的部分：
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) the tabid and rtabid (for referencing constraints) from (1) can now be used to get the columns of the tables like that:
(3) (1) 中的 tabid 和 rtabid（用于引用约束）现在可用于获取表的列，如下所示：
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 the constrtype is 'C', then get the check information like this:
(4) 如果constrtype为'C'，则获取校验信息如下：
SELECT type, seqno, checktext
FROM 'informix'.syschecks
WHERE constrid = ? -- constrid from (1)
Quite hairy indeed
确实毛茸茸的
