OraFAQ Forum: SQL & PL/SQL » Differentiating NOT NULL and CHECK constraints

Home » SQL & PL/SQL » SQL & PL/SQL » Differentiating NOT NULL and CHECK constraints

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Differentiating NOT NULL and CHECK constraints [message #158515]

Sun, 12 February 2006 18:03

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Long time responder - first time as OP... Smile

Looking in the DBA_CONSTRAINTS view, NOT NULL constraints show as just another CHECK constraint. The CONSTRAINT_TYPE is 'C', and the SEARCH_CONDITION is "COL_NAME" IS NOT NULL.

Looking at this view, you cannot really tell a NOT NULL constraint from any other CHECK constraint. In fact, if you created a CHECK constraints with the syntax "COL_NAME" IS NOT NULL then it would be impossible to tell.

SQL*Loader direct-path load option CAN tell the difference though. It disables (by default) CHECK constraints, but evaluates NOT NULL constraints. How does it do it?

I realise that DBA_TAB_COLUMNS.NULLABLE tells us which columns are NOT NULL, but there is no way (I know) to match this information up to DBA_CONSTRAINTS.

What I want to do is this:

CREATE TABLE TABB AS SELECT * FROM TABA where...

creates a table with NOT NULL constraints enabled automatically. Then I want to go through and add all of the TABA constraints to TABB. I can go to USER_CONSTRAINTS and use DBMS_METADATA to get the ALTER TABLE syntax, but I need to filter out all of the NOT NULL constraints that are already there.

Ideas?
_____________
Ross Leishman

Report message to a moderator

Re: Differentiating NOT NULL and CHECK constraints [message #158550 is a reply to message #158515]

Mon, 13 February 2006 01:59

Frank
Messages: 7901
Registered: March 2000

Senior Member

Could it be that the column "GENERATED" (Oracle XE) can tell you?

SQL> alter table a add constraint check ("COL4" is not null);
alter table a add constraint check ("COL4" is not null)
                                                      *
ERROR at line 1:
ORA-02438: Column check constraint cannot reference other columns


SQL> alter table a add constraint z check ("COL4" is not null);

Table altered.

Seems like a not null check constraint must be named.

hth

Report message to a moderator

Re: Differentiating NOT NULL and CHECK constraints [message #158649 is a reply to message #158550]

Mon, 13 February 2006 15:46

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Interesting, but no cigar for 10g

CDMDEV@CDMDEV> create table test (
  2  a number not null check ("A" IS NOT NULL)
  3  );

Table created.

CDMDEV@CDMDEV> select * from user_constraints
  2  where table_name = 'TEST';

OWNER                          CONSTRAINT_NAME                C
------------------------------ ------------------------------ -
TABLE_NAME
------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
R_OWNER                        R_CONSTRAINT_NAME              DELETE_RU STATUS
------------------------------ ------------------------------ --------- --------
DEFERRABLE     DEFERRED  VALIDATED     GENERATED      BAD RELY LAST_CHAN
-------------- --------- ------------- -------------- --- ---- ---------
INDEX_OWNER                    INDEX_NAME                     INVALID
------------------------------ ------------------------------ -------
VIEW_RELATED
--------------
CDMDEV                         SYS_C0042267                   C
TEST
"A" IS NOT NULL
                                                                        ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED     GENERATED NAME          14-FEB-06



CDMDEV                         SYS_C0042268                   C
TEST
"A" IS NOT NULL
                                                                        ENABLED
NOT DEFERRABLE IMMEDIATE VALIDATED     GENERATED NAME          14-FEB-06

_____________
Ross Leishman

Report message to a moderator

Re: Differentiating NOT NULL and CHECK constraints [message #158652 is a reply to message #158649]

Mon, 13 February 2006 15:59

rleishman
Messages: 3728
Registered: October 2005
Location: Melbourne, Australia

Senior Member

Gave me an idea. Answered my own question now:

  1  select *
  2  from sys.cdef$
  3  where con# in (
  4     select con#
  5     from sys.con$
  6     where name in ('SYS_C0042267','SYS_C0042268')
  7* )
CDMDEV@CDMDEV> /

      CON#       OBJ#       COLS      TYPE#      ROBJ#      RCON# RRU     MATCH#
---------- ---------- ---------- ---------- ---------- ---------- --- ----------
    REFACT    ENABLED CONDLENGTH
---------- ---------- ----------
CONDITION
--------------------------------------------------------------------------------
   INTCOLS MTIME          DEFER     SPARE1     SPARE2     SPARE3
---------- --------- ---------- ---------- ---------- ----------
SPARE4
--------------------------------------------------------------------------------
SPARE5
--------------------------------------------------------------------------------
SPARE6
---------
     42268      84771          1          1
                    1         15
"A" IS NOT NULL
         1 14-FEB-06         12          6




     42267      84771          1          7
                    1         15
"A" IS NOT NULL
         1 14-FEB-06         12          6

CDEF$.TYPE# has values 7 or 1, depending on whether it is a NOT NULL or an explicit CHECK constraint respectively.
_____________
Ross Leishman

Report message to a moderator

Previous Topic:	Order of a Columns in table
Next Topic:	how to fetch a varray element

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Jan 18 21:45:17 CST 2026