Find only user-defined check constraints in Oracle ALL_CONSTRAINTS

Question

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

Lukas Eder · Answer 1 · 2019-03-29T13:11:51.043

3

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.

edited Mar 29 '19 at 13:11

answered Mar 29 '19 at 12:10

Lukas Eder

211,314
129
689
1,509

in 12c there is a column GENERATED that could help? – Mar 29 '19 at 12:24
is this your code or your customers'? If yours, you could use always used named constraints for explicits, if the constraint name is system generated, then there's your implicit not null – thatjeffsmith Mar 29 '19 at 12:27
@ik_zelf: I've included the `GENERATED` column as you can see in the examples. It doesn't help. My explicit check constraints are unnamed, and thus have the same value `GENERATED NAME` as the implicit check constraint. – Lukas Eder Mar 29 '19 at 12:38
@thatjeffsmith: It's not mine. – Lukas Eder Mar 29 '19 at 12:38
curious question though - i consider all of your examples but the 3rd one to be explicit NOT NULL constraints, another implicit one would be a PRIMARY KEY constraint for example – thatjeffsmith Mar 29 '19 at 13:40
@thatjeffsmith: They're not the same to the optimiser. Add an index to columns `a` and `b` each, and check out execution plans for `SELECT 1 FROM dual WHERE 1 NOT IN (SELECT a FROM foo)` and `SELECT 1 FROM dual WHERE 1 NOT IN (SELECT b FROM foo)`. For your convenience: https://i.imgur.com/TaNXUuZ.png – Lukas Eder Mar 29 '19 at 13:50
ok sure, but i was thinking like a human, not like a computer :) – thatjeffsmith Mar 29 '19 at 13:55
@thatjeffsmith: The human that has to debug why that other human's query is so crazy slow (full table scan rather than index range scan for the anti join) will thank me for having pointed out the difference here ;-) Btw, the primary key doesn't generate an entry in `ALL_CONSTRAINTS`, curiously. – Lukas Eder Mar 29 '19 at 13:56

Lukasz Szozda · Answer 2 · 2019-03-29T22:22:13.320

Idea: You could compare table with its "shadow" counterpart. CREATE TABLE AS does not preserve user defined check constraints:

-- original table
CREATE TABLE foo (
  id int PRIMARY KEY NOT NULL,
  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
  d INT CONSTRAINT my_check CHECK (d = 3)
);

-- clone without data(it should be stored in different schema than actual objects)
CREATE TABLE shadow_foo
AS
SELECT * 
FROM foo 
WHERE 1=2;

-- for Oracle 18c you could consider private temporary tables
CREATE PRIVATE TEMPORARY TABLE ora$shadow_foo ON COMMIT DROP DEFINITION
AS
SELECT * FROM foo WHERE 1=2;

And main query:

SELECT c.*
FROM (SELECT * FROM all_constraints WHERE TABLE_NAME NOT LIKE 'SHADOW%') c
LEFT JOIN (SELECT * FROM all_constraints WHERE TABLE_NAME LIKE 'SHADOW%') c2
  ON c2.table_name = 'SHADOW_' || c.table_name
 AND c2.owner = c.owner
 AND c2.search_condition_vc = c.search_condition_vc
WHERE c2.owner IS NULL
  AND c.constraint_type = 'C'
  AND c.owner  LIKE 'FIDDLE%'

db<>fiddle demo

That's quite clever - although not practical in my case. – Lukas Eder Apr 01 '19 at 09:03 — Lukas Eder, Apr 01 '19 at 09:03

score 2 · Accepted Answer · answered Mar 29 '19 at 23:07

SYS.CDEF$.TYPE# knows the difference between implicit and explicit check constraints. Implicit check constraints are stored as 7, explicit check constraints are stored as 1.

--Explicit constraints only.
select constraint_name, search_condition
from dba_constraints
where (owner, constraint_name) not in
    (
        --Implicit constraints.
        select dba_users.username, sys.con$.name
        from sys.cdef$
        join sys.con$
            on cdef$.con# = con$.con#
        join dba_users
            on sys.con$.owner# = dba_users.user_id
        where cdef$.type# = 7
    )
    and constraint_type = 'C'
    and table_name = 'FOO'
order by 1;


CONSTRAINT_NAME   SEARCH_CONDITION
---------------   ----------------
SYS_C00106940     b is not null
SYS_C00106941     c > 1

This solution has the obvious disadvantage of relying on undocumented tables. But it does appear to be more accurate than relying on the text of the condition. Some implicit check constraints are not created with double quotes. I can't reproduce that issue, but I found it happening to the table SYS.TAB$.

While I unfortunately cannot rely on being able to query those tables due to possibly lacking grants, this is definitely a much more robust solution than mine, in case someone else finds this question. — Lukas Eder, Apr 01 '19 at 09:02

Find only user-defined check constraints in Oracle ALL_CONSTRAINTS

3 Answers3