Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Find only user-defined check constraints in Oracle ALL_CONSTRAINTS

Consider this table definition:

CREATE TABLE foo (
  a int not null,              -- Implicit not null constraint
  b int check (b is not null), -- Explicit not null constraint
  c int check (c > 1)          -- Explicit constraint
);

I want to discover all the explicit check constraints, i.e. constraints that the user defined in their DDL statement by using the CHECK syntax. Those constraints may or may not be named. In the above example, they're not named. How can I discover only the "explicit" check constraints, ignoring the implicit ones?

E.g. when I query ALL_CONSTRAINTS:

SELECT *
FROM all_constraints
WHERE constraint_type = 'C'
AND table_name = 'FOO';

I don't see any way to distinguish the explicitness/implicitness:

CONSTRAINT_NAME   SEARCH_CONDITION   GENERATED
---------------------------------------------------
SYS_C00120656     "A" IS NOT NULL    GENERATED NAME
SYS_C00120657     b is not null      GENERATED NAME
SYS_C00120658     c > 1              GENERATED NAME
like image 239
Lukas Eder Avatar asked Mar 04 '23 06:03

Lukas Eder


1 Answers

I could of course make a heuristic on the unlikelyhood of someone using the exact "COLUMN_NAME" IS NOT NULL syntax (including double quote):

SELECT *
FROM all_constraints
WHERE constraint_type = 'C'
AND table_name = 'FOO'
AND search_condition_vc NOT IN (
  SELECT '"' || column_name || '" IS NOT NULL'
  FROM all_tab_cols
  WHERE table_name = 'FOO'
  AND nullable = 'N'
);

This gives me the wanted result:

CONSTRAINT_NAME   SEARCH_CONDITION   GENERATED
---------------------------------------------------
SYS_C00120657     b is not null      GENERATED NAME
SYS_C00120658     c > 1              GENERATED NAME

I'm putting this as an answer here, as this might be good enough for some people, but I'd really like a more reliable solution.

like image 131
Lukas Eder Avatar answered Mar 07 '23 04:03

Lukas Eder