Saturday, 7 October 2017

NOT NULL Constraint Example


StepsCommand
Create table with NOT NULL ConstraintCREATE 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 createdDESC 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

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



No comments:

Post a Comment