Oracle NOT NULL Constraint

Oracle NOT NULL constraint is used to prevent or stops null values entry in a column of a table. By default, a column can hold NULL values but to prevent this we have to use NOT NULL constraint. In all relational databases, the NOT NULL constraint doesn’t accept null values but it accepts duplicate values. The NOT NULL constraint is only allowed at the column level, not at the table level.

The syntax for the NOT NULL column:-
columnName datatype(size) NOT NULL;

Syntax to create a new table and columns having NOT NULL constraint,
CREATE TABLE tableName (
col1 datatype(size) NOT NULL,
col2 datatype(size) NOT NULL,
……
);

There can be multiple columns in a table in Oracle database having NOT NULL constraint which doesn’t accept null values.

To check NOT NULL constraint is applied on the column or not, we can use DESC or DESCRIBE command. It is a SQL *Plus tool-specific command and only works in SQL *Plus tool. The DESC command gives information about the table like column names, its data type, and the column have NOT NULL constraint or not.

SQL> DESC tableName;

Example

SQL> CREATE TABLE test (
       sno NUMBER(10) NOT NULL,
       name VARCHAR2(10)
     );
Table created.

In the test table, two columns “sno” and “name” are there. The sno column having NOT NULL constraint so we are not allowed to insert a null value in that column.

SQL> DESC test;
 Name    Null?    Type
 ------- -------- ------------
 SNO     NOT NULL NUMBER(10)
 NAME             VARCHAR2(10)

Now, let us insert the values to the table.

SQL> INSERT INTO test VALUES(NULL, 'xyz');
INSERT INTO test VALUES(NULL, 'xyz')
                        *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("SCOTT"."TEST"."SNO")

The NOT NULL constraint is applied only on sno column, not on the name column. So, we can’t insert null value on sno column but the null value can be inserted on the name column.

SQL> INSERT INTO test VALUES(1, NULL);
SQL> INSERT INTO test VALUES(1, 'xyz');
SQL> INSERT INTO test VALUES(1, 'xyz');
SQL> INSERT INTO test VALUES(2, 'xyz');
SQL> INSERT INTO test VALUES(2, 'abc');
SQL> SELECT * FROM test;

       SNO NAME
---------- ----------
         1
         1 xyz
         1 xyz
         2 xyz
         2 abc

The NOT NULL constraint accepts duplicate values. Therefore, we can insert duplicate values to the “sno” column of test table.

Add NOT NULL Constraint on Oracle Table

The NOT NULL constraint can be applied on the an existing column of a table or we can add a new column with NULL constraint.

Enable NOT NULL constraint on an existing column

In Oracle if we want to enable NOT NULL constraint on an existing column of a table then we are using ALTER … MODIFY.

Condition:- The column must not contain any NULL values. Otherwise Oracle gives error:- ORA-02296: cannot enable – null values found

Syntax to enable NOT NULL constraint on an existing column of a table:-
ALTER TABLE tablename
MODIFY columname NOT NULL;

Example:-

SQL> CREATE TABLE student(
       sno NUMBER(10)
     );
Table created.

SQL> DESC student;
 Name    Null?    Type
 ------- -------- ----------
 SNO              NUMBER(10)

Applying NOT NULL constraint on an existing column sno of student table,

SQL> ALTER TABLE student 
     MODIFY sno NOT NULL;
Table altered.

SQL> DESC student;
 Name    Null?    Type
 ------- -------- ----------
 SNO     NOT NULL NUMBER(10)

Note:- At a time, NOT NULL constraint can be enabled only for one column, we can’t enable NULL NOT constraints more than one column in a single statement.

Enable NOT NULL constraint without validating the existing column

If we have a column that contains some null values, but we want to enable NOT NULL constraint without validating and disturbing the existing values of the column then we have to use NOVALIDATE along with NOT NULL constraint.

When we use NOVALIDATE then it won’t check existing values in the column but validate new insert or update values. Syntax:-
ALTER TABLE tablename
MODIFY columname NOT NULL NOVALIDATE;

Example:-

SQL> CREATE TABLE test1(
       sno NUMBER(10),
       name VARCHAR2(15)
     );
Table created.

SQL> INSERT INTO test1 VALUES(1, NULL);
SQL> INSERT INTO test1 VALUES(1, 'abc');
SQL> INSERT INTO test1 VALUES(2, NULL);
SQL> SELECT * FROM test1;
       SNO NAME
---------- ---------------
         1
         1 abc
         2

SQL> ALTER TABLE test1 MODIFY
     name NOT NULL;
ERROR at line 1:
ORA-02296: cannot enable (SCOTT.) 
- null values found

SQL> ALTER TABLE test1 MODIFY
     name NOT NULL NOVALIDATE;
Table altered.

SQL> INSERT INTO test1 VALUES(2, 'pqr');
1 row created.

SQL> INSERT INTO test1 VALUES(5, NULL);
ERROR at line 1:
ORA-01400: cannot insert NULL into 
("SCOTT"."TEST1"."NAME")

SQL> SELECT * FROM test1;
       SNO NAME
---------- ---------------
         1
         1 abc
         2
         2 pqr

Add a new column with NOT NULL constraint

We can also add a new column with NOT NULL constraint on an existing table. For this we have to follow the below syntax:-

Syntax to add a new column with NOT NULL constraint in Oracle database,
ALTER TABLE tablename ADD
columnname datatype(size) NOT NULL;

Example:- Add a new column “address” in the student table with NOT NULL constraint.

SQL> ALTER TABLE student ADD
     address VARCHAR2(20) NOT NULL;
Table altered.

SQL> DESC student;
 Name    Null?    Type
 ------- -------- ----------
 SNO     NOT NULL NUMBER(10)
 ADDRESS NOT NULL VARCHAR2(20)

Note:- Whenever we are copying a table from another table then, with its data NOT NULL constraints are also copied. So, in this case, no need to add NOT NULL constraint manually in the new table.

SQL> CREATE TABLE test1
     AS
     SELECT * FROM student;
Table created.

SQL> DESC test1;
 Name    Null?    Type
 ------- -------- ----------
 SNO     NOT NULL NUMBER(10)
 ADDRESS NOT NULL VARCHAR2(20)

Remove NOT NULL Constraint in Oracle

Condition:- We can remove/disable NOT NULL constraint from an existing column but that column must not be used in a PRIMARY KEY constraint.

In Oracle, when column is changed to allow nulls then Oracle database automatically drop the NOT NULL constraints.

Syntax to allow NULL value on the existing column:-
ALTER TABLE tablename MODIFY(columnName NULL);

Example to remove NOT NULL constraints from sno and address columns of test1 table:-

SQL> ALTER TABLE test1 
     MODIFY (sno NULL, address NULL);
Table altered.

SQL> DESC test1;
 Name    Null?    Type
 ------- -------- ----------
 SNO              NUMBER(10)
 ADDRESS          VARCHAR2(20)

If you enjoyed this post, share it with your friends. Do you want to share more information about the topic discussed above or do you find anything incorrect? Let us know in the comments. Thank you!

Also Learn,

• Constraints in Oracle
• Rename Constraint
• Disable Constraint
• Drop Constraint

Search