Re: Difference between table level and column level NOT NULL constraint?

Vishalm wrote:
>
> Hi,
>
> What is the difference between table level and column level NOT NULL
> constraint?
>
> for e.g.a not null constraint can be defined in the foll ways
>
> 1)
>
> Create table test
> (testnull VARCHAR2(10) NOT NULL)
>
> 2)
>
> Create table test
> (testnull VARCHAR2(10));
>
> ALTER TABLE ADD CHECK (testnull NOT NULL);
>
> Is there any difference in the way Oracle does validation for the
> above defined constraint methods or are there any performance
> differences?
>

There are indeed two differences:

SQL> create table nulltest (x number, y number not null);  

Tabelle wurde angelegt.  

SQL> alter table nulltest add check (x is not null);  

Tabelle wurde geändert.  

SQL> insert into nulltest values (NULL, 3); insert into nulltest values (NULL, 3)
*
FEHLER in Zeile 1:
ORA-02290: Verstoß gegen CHECK-Regel (AMIS.SYS_C00958)    

SQL> insert into nulltest values (3, NULL); insert into nulltest values (3, NULL)
*
FEHLER in Zeile 1:
ORA-01400: Einfügen von NULL in ("AMIS"."NULLTEST"."Y") nicht möglich

SQL> select column_name, NULLABLE from user_tab_columns where   2 table_name = 'NULLTEST';  

COLUMN_NAME                    N
------------------------------ -
X                              Y
Y                              N

• The Oracle error number you should catch in your program is different.
• The data dictionary shows different attributes for the columns.

I guess an inline NOT NULL constraint is faster, but try it yourself.

Uwe

> Thanx in advance.
>
> regards,
> Pradeep
>
> (Pls cc ur response to : pradeep.mittal_at_iflexsolutions.com)

-- 
Uwe Schneider       | Telefon +49 7244 / 609504
Haydnstr. 1         | Mail    uwe_at_richard-schneider.de
DE-76356 Weingarten | http://www.richard-schneider.de/uwe
Linux - OS al dente!