Tom,

your answer is very much practical. But is it possible to find the difference by referring user_constraints ?

For e.g. I have created following table
17:00:20 atlas@ATP1P1> create table parag
17:03:17 2 ( a number not null,
17:03:24 3 b number(2) check ( b > 10 ));

Table created.

select OWNER, CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, DEFERRABLE, DEFERRED
from user_constraints where table_name = 'PARAG'

It is giving me following output

CONSTRAINT_NAME C TABLE_NAME DEFERRABLE DEFERRED
------------------------------ - ------------------------------ -------------- ---------
SYS_C00255287 C PARAG NOT DEFERRABLE IMMEDIATE
SYS_C00255288 C PARAG NOT DEFERRABLE IMMEDIATE

I am sure it is possible but I do not know how !!! Kindly help me.

regards

Tom,

Sorry I had not mentioned one thing in my question. I do not want to give constraint names to not null and check. Then how to find not null and check constraints from user_constranits ?

regards & thanks

Hi Tom,

I fully agree with you that it is utter wast of time and energy. But I have faced similar kind of question in Oracle9i DBA OCP exam for fundamentals-1 and I was not in position to arrive the correct answer. I have asked this question for my learning purpose only. ( I do not recollect exact question but it is sure, it is on the same line and in SQL it does not mentioned search_condition, I do not know if search_condition not mentioned you can arrive the answer !!! ).

Will you pl send some time to explain your answer/explaniation with example on such stupid question ?

( I have also not understood --> table level check for joining user_cons_columns. How to defined not null or check for table level ? )

regards & thanks

You wrote:
One of my co-workers was working on a script that would extract DDL.

Why would you want to waste time doing that? Oracle does this for you through OEM.

Anyhow, this is what I did. And Tom please fee free to contribute if I made any errors.

MDINH@dev> CREATE TABLE T_NN(ID NUMBER NOT NULL);

Table created.

MDINH@dev> CREATE TABLE T_CK(ID NUMBER);

Table created.

MDINH@dev> ALTER TABLE T_CK
2 ADD (CONSTRAINT CHECK(ID IS NOT NULL)) ;
ADD (CONSTRAINT CHECK(ID IS NOT NULL))
*
ERROR at line 2:
ORA-02438: Column check constraint cannot reference other columns

NOTICE: In order to add a check constraint, I had to provide a name for the constraint. The constraint cannot be named by the system. Am I correct on this Tom?

MDINH@dev> ALTER TABLE T_CK
2 ADD (CONSTRAINT T_CK_NN CHECK(ID IS NOT NULL)) ;

Table altered.

MDINH@dev> SELECT TABLE_NAME, COLUMN_NAME, NULLABLE
2 FROM USER_TAB_COLS WHERE TABLE_NAME LIKE 'T_%';

TABLE_NAME COLUMN_NAME N
------------------------------ ------------------------------ -
T_CK ID Y
T_NN ID N

The value for NULLABLE for T_CK is Y.

MDINH@dev> SELECT CONSTRAINT_NAME, CONSTRAINT_TYPE, TABLE_NAME, SEARCH_CONDITION
2 FROM USER_CONSTRAINTS WHERE TABLE_NAME LIKE 'T_%'
3 ;

CONSTRAINT_NAME C TABLE_NAME
------------------------------ - ------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
T_CK_NN C T_CK
ID IS NOT NULL

SYS_C003175 C T_NN
"ID" IS NOT NULL

However, there is a check constraint that prevents NULL data. Notice the distinction, Oracle places quotes around column name.

Apparently, distinguishing NN from other CHECK constraints is no mean trick. I created this table, and used various tools to extract the DDL; I got various results!

The original DDL:
22:02:20 GANOTEDP\nn> create table test_nn
( n number constraint nn_n not null,
y number constraint ck_y check(Y IS NOT NULL) );

Table created.

Elapsed: 00:00:04.06
22:04:40 GANOTEDP\nn> desc test_nn;
Name Null? Type
---------------------------------- -------- ---------
N NOT NULL NUMBER
Y NUMBER

EXP produced:
CREATE TABLE "TEST_NN" ("N" NUMBER CONSTRAINT "NN_N" NOT NULL ENABLE, "Y" NUMBER)
ALTER TABLE "TEST_NN" ADD CONSTRAINT "CK_Y" CHECK (Y IS NOT NULL) ENABLE NOVALIDATE
ALTER TABLE "TEST_NN" ENABLE CONSTRAINT "CK_Y"

One vendor tool couldn't distinguish the difference between CHECK and NOT NULL:
create table NN.TEST_NN
(
N NUMBER,
Y NUMBER
);
alter table NN.TEST_NN
add constraint CK_Y
check (Y IS NOT NULL);
alter table NN.TEST_NN
add constraint NN_N
check ("N" IS NOT NULL);

Another tool understood the difference, but left the NN unnamed:
CREATE TABLE test_nn
(n NUMBER NOT NULL,
y NUMBER)
/
ALTER TABLE test_nn
ADD CONSTRAINT ck_y CHECK (Y IS NOT NULL)
/

And worst of all, one data-modeling tool totally missed the check constraint (even though I specifically asked for check constraints during the reverse-engineering process) as well as leaving the NN unnamed!
CREATE TABLE TEST_NN (
N NUMBER NOT NULL,
Y NUMBER NULL
);

Of course, all this may vary by tool and tool version, but it was an eye-opener for me!

hi Tom,

What is the exact difference between

where a != NULL
and
where a IS NOT NULL

Is it only NULL denotes an undefined value so we cant use not equal sign?