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
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$
.