Is there a lookup table for the column ALL_CONSTRAINTS.CONSTRAINT_TYPE in Oracle DB?

Question

I Googled around and can't seem to find if there is a reference/lookup table for the column ALL_CONSTRAINTS.CONSTRAINT_TYPE. I saw this answer but the source of the answer isn't put forward. The other answer refers to the documentation, but the documentation doesn't say whether there is a lookup table. I can see the display values for CONSTRAINT_TYPE in VS Code SQL Developer extension when pulling up the constraints for a given table.

Again, I googled around, but nothing seems to be coming up on this, other than that documentation.

Answer 1

There's none, as far as I can tell.

If you have a look at CREATE VIEW script:

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

you'll see that constraint_type is simply decoded as

          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',
                  '?'),

If there were a lookup table, it would have probably been used in the FROM clause and joined to other table(s).