如何从Informix中的名称获取约束详细信息?

malias 发布于 2018-05-25 constraints 最后更新 2018-05-25 12:29 125 浏览

当编写大型事务(大量插入,删除,更新)并因此违反Informix中的约束(v10,但也应用于其他版本)时,我收到了一条不太有用的消息,例如,我违反了约束条件r190_710。我怎样才能找出哪个表和关键字被一定的约束所覆盖?我只知道这个名字?

已邀请:

uqui

赞同来自:

Informix Guide to SQL: Reference看来,您应该查看系统目录表SYSCONSTRAINTS和SYSINDICES。

gnihil

赞同来自:

托尼安德鲁斯建议(指出URL的另一个终点):

From Informix Guide to SQL: Reference it appears you should look at the system catalog tables SYSCONSTRAINTS and SYSINDICES.
Informix系统目录在该手册中进行了介绍。 SysConstraints表是分析约束的起点,当然,您可以在该表中找到约束名称,然后从中找出其他详细信息。 但是,您还必须查看其他表,而不仅仅是(甚至直接)SysIndices。 例如,我在数据库中的表上有很多NOT NULL约束。对于那些,约束类型是'N',并且不需要在别处寻找更多信息。 约束类型'P'表示主键;那需要通过SysIndexes视图或SysIndices表进行更多的分析。同样,约束类型'U'表示唯一约束,并需要SysIndexes视图或SysIndices表中的额外信息。 约束类型'C'表示检查约束;在SysChecks表中找到约束的文本(和二进制编译后的表格)(数据类型'T'和'B';数据或多或少用Base-64编码,尽管没有'='填充最后在62和63上使用不同的字符)。 最后,约束类型'R'表示参照完整性约束。您可以使用SysReferences表来找出引用哪个表,然后使用SysIndexes或SysIndices来确定引用表和引用表上的哪些索引已被使用,并从中可以找到相关的列。这可能会变得非常多毛!

malias

赞同来自:

从浏览www.iiug.org(国际Informix用户组),我发现这个不太容易的解决方案。 (1)从约束名称获取引用约束数据(通过将“AND st.tabname MATCHES?”替换为“AND sc.constrname =?”,您可以获取表的所有约束)。该声明选择了比这里所需的更多的字段,因为它们在其他情况下可能会很有趣。

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)使用part1-part16来确定哪个列受到约束的影响:包含不同于0的值的part [n]包含使用列的列号。使用(3)找到列的名称。 如果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)现在可以使用来自(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)如果constrtype是'C',那么得到这样的检查信息:
SELECT type, seqno, checktext
FROM 'informix'.syschecks
WHERE constrid = ? -- constrid from (1)
确实很多毛

mneque

赞同来自:

表中的列与他们的约束

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

wiusto

赞同来自:

得到受约束“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" )
)
);

xqui

赞同来自:

如果你的约束被命名为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;

eomnis

赞同来自:

我一直在使用以下查询来获取有关不同类型约束的更多信息。 它基于系统表中的一些洞察以及关于系统目录的几个解释。 sysconstraints.constrtype指示约束的类型:

  • 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'