主页 / coding / 问题 / 79149364
Accepted
JD136
Asked: 2024-11-02 04:37:35 +0800 CST

Oracle DB 中是否有针对 ALL_CONSTRAINTS.CONSTRAINT_TYPE 列的查找表？

  • 772

我在 Google 上搜索了一下，似乎找不到 ALL_CONSTRAINTS.CONSTRAINT_TYPE 列是否有引用/查找表。我看到了这个答案，但没有提供答案的来源。另一个答案提到了文档，但文档没有说明是否有查找表。在拉出给定表的约束时，我可以在 VS Code SQL Developer 扩展中看到 CONSTRAINT_TYPE 的显示值。
在 VS Code 的 SQL Developer 扩展中拉出表格时看到的约束图片

再次，我到处搜索，但除了那份文档之外，似乎没有任何结果。

oracle-database

2 个回答

  1. Best Answer

    据我所知，没有。

    如果你看一下CREATE VIEW脚本：

    CREATE OR REPLACE FORCE VIEW SYS.ALL_CONSTRAINTS
(
   OWNER,
   CONSTRAINT_NAME,
   CONSTRAINT_TYPE,
   TABLE_NAME,
   SEARCH_CONDITION,
   R_OWNER,
   R_CONSTRAINT_NAME,
   DELETE_RULE,
   STATUS,
   DEFERRABLE,
   DEFERRED,
   VALIDATED,
   GENERATED,
   BAD,
   RELY,
   LAST_CHANGE,
   INDEX_OWNER,
   INDEX_NAME,
   INVALID,
   VIEW_RELATED
)
AS
   SELECT ou.name,
          oc.name,
          DECODE (c.type#,
                  1, 'C',
                  2, 'P',
                  3, 'U',
                  4, 'R',
                  5, 'V',
                  6, 'O',
                  7, 'C',
                  8, 'H',
                  9, 'F',
                  10, 'F',
                  11, 'F',
                  13, 'F',
                  '?'),
          o.name,
          c.condition,
          ru.name,
          rc.name,
          DECODE (c.type#,
                  4, DECODE (c.refact,  1, 'CASCADE',  2, 'SET NULL',  'NO ACTION'),
                  NULL),
          DECODE (c.type#, 5, 'ENABLED', DECODE (c.enabled, NULL, 'DISABLED', 'ENABLED')),
          DECODE (BITAND (c.defer, 1), 1, 'DEFERRABLE', 'NOT DEFERRABLE'),
          DECODE (BITAND (c.defer, 2), 2, 'DEFERRED', 'IMMEDIATE'),
          DECODE (BITAND (c.defer, 4), 4, 'VALIDATED', 'NOT VALIDATED'),
          DECODE (BITAND (c.defer, 8), 8, 'GENERATED NAME', 'USER NAME'),
          DECODE (BITAND (c.defer, 16), 16, 'BAD', NULL),
          DECODE (BITAND (c.defer, 32), 32, 'RELY', NULL),
          c.mtime,
          DECODE (c.type#,  2, ui.name,  3, ui.name,  NULL),
          DECODE (c.type#,  2, oi.name,  3, oi.name,  NULL),
          DECODE (BITAND (c.defer, 256),
                  256, DECODE (c.type#,
                               4, CASE
                                     WHEN (   BITAND (c.defer, 128) = 128
                                           OR o.status IN (3, 5)
                                           OR ro.status IN (3, 5))
                                     THEN
                                        'INVALID'
                                     ELSE
                                        NULL
                                  END,
                               CASE
                                  WHEN (   BITAND (c.defer, 128) = 128
                                        OR o.status IN (3, 5))
                                  THEN
                                     'INVALID'
                                  ELSE
                                     NULL
                               END),
                  NULL),
          DECODE (BITAND (c.defer, 256), 256, 'DEPEND ON VIEW', NULL)
     FROM sys.con$                    oc,
          sys.con$                    rc,
          sys."_BASE_USER"            ou,
          sys."_BASE_USER"            ru,
          sys."_CURRENT_EDITION_OBJ"  ro,
          sys."_CURRENT_EDITION_OBJ"  o,
          sys.cdef$                   c,
          sys.obj$                    oi,
          sys.user$                   ui
    WHERE     oc.owner# = ou.user#
          AND oc.con# = c.con#
          AND c.obj# = o.obj#
          AND c.type# != 8
          AND (   c.type# < 14
               OR c.type# > 17)                         /* don't include supplog cons   */
          AND (c.type# != 12)                           /* don't include log group cons */
          AND c.rcon# = rc.con#(+)
          AND c.enabled = oi.obj#(+)
          AND oi.owner# = ui.user#(+)
          AND rc.owner# = ru.user#(+)
          AND c.robj# = ro.obj#(+)
          AND (   o.owner# = USERENV ('SCHEMAID')
               OR o.obj# IN (SELECT obj#
                               FROM sys.objauth$
                              WHERE grantee# IN (SELECT kzsrorol FROM x$kzsro))
               OR                                         /* user has system privileges */
                  EXISTS
                     (SELECT NULL
                        FROM v$enabledprivs
                       WHERE priv_number IN (-45                      /* LOCK ANY TABLE */
                                                ,
                                             -47                    /* SELECT ANY TABLE */
                                                ,
                                             -48                    /* INSERT ANY TABLE */
                                                ,
                                             -49                    /* UPDATE ANY TABLE */
                                                ,
                                             -50                    /* DELETE ANY TABLE */
                                                )));

COMMENT ON TABLE SYS.ALL_CONSTRAINTS IS 'Constraint definitions on accessible tables';

COMMENT ON COLUMN SYS.ALL_CONSTRAINTS.CONSTRAINT_TYPE IS 'Type of constraint definition';

    你会发现它constraint_type被解码为

              DECODE (c.type#,
                  1, 'C',
                  2, 'P',
                  3, 'U',
                  4, 'R',
                  5, 'V',
                  6, 'O',
                  7, 'C',
                  8, 'H',
                  9, 'F',
                  10, 'F',
                  11, 'F',
                  13, 'F',
                  '?'),

    如果有一个查找表，它很可能在子句中使用FROM并连接到其他表。

    • 2

  2. 没有查找表。Oracle 在许多情况下只使用字符串，如您在 中看到的ALL_CONSTRAINTS.CONSTRAINT_TYPE。他们使用Y/N而不是0/1等

    • 1

相关问题