NOT NULL Constraint Example

Steps	Command
Create table with NOT NULL Constraint	CREATE TABLE SampleNN01(SampID NUMBER(2)CONSTRAINT SampleNN01_SampID_NN NOT NULL,SampName VARCHAR2(10) CONSTRAINT SampleNN01_SampName_NN NOT NULL,SampDate DATE);
See how many constraint created	DESC SampleNN01;
Now insert  data	Try to Insert below data into this SampleNN01 Table

Insert Data Into SampleNN01 Table and the behavior of NOT NULL constraint

SQL> INSERT INTO SampleNN01 VALUES(1, 'SAMPLE01', SYSDATE);

1 row created.

SQL> INSERT INTO SampleNN01 VALUES(NULL, 'SAMPLE01', SYSDATE);

VALUES(NULL, 'SAMPLE01', SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLENN01"."SAMPID")

SQL> INSERT INTO SampleNN01 VALUES(2, NULL, SYSDATE);

VALUES(2, NULL, SYSDATE) * ERROR at line 2: ORA-01400: cannot insert NULL into ("SCOTT"."SAMPLENN01"."SAMPNAME")

SQL> INSERT INTO SampleNN01 VALUES(2, 'SAMPLE02', NULL);

1 row created.

Now creating Table SampleNN02 with table level NOT NULL Constraint:

SQL> CREATE TABLE SampleNN02(SampID NUMBER(2),SampName VARCHAR2(10) CONSTRAINT SampleNN02_SampName_NN NOT NULL,SampDate DATE,CONSTRAINT SampleNN02_SampID_NN NOT NULL(SampID));

CONSTRAINT SampleNN02_SampID_NN NOT NULL(SampID)
*
ERROR at line 7:
ORA-00904: : invalid identifier

[NOT NULL Constraint is the only constraint which should be declare in Column Level.]


See the below ORA-01400 details

Error	Details
ORA-01400: cannot insert NULL into	If we are trying to insert NULL value in a column which is having NOT NULL Constraint then we wil get ORA-01400. Try to insert value other than NULL.