NOT NULL Constraint in Oracle

Back to: Oracle Tutorials for Beginners and Professionals

NOT NULL Constraint in Oracle with Examples

In this article, I am going to discuss NOT NULL Constraint in Oracle with Examples. Please read our previous article where we discussed the UNIQUE Constraint in Oracle with Examples.

NOT NULL Constraint in Oracle:

By default, the database column stores NULL value means no value in the data row. By defining NOT NULL Constraint to the table column, the default behavior of the database column changes and it does not accept NULL values.

In the database table, we can have some columns where storing NULL values doesn’t make any sense. So, if you want any column not to accept NULL value then you need to apply the NOT NULL constraint to that column in Oracle.

So, in other words, we can say that this NOT NULL Constraint in Oracle is used to restrict nulls but accept duplicate values into a column.

In Oracle, a table can contain any number of NOT NULL Constraints. The NOT NULL Constraints can be defined to string as well as numeric data types such as INT, VARCHAR, etc. The NOT NULL constraint does not support “table-level”. It can only be applied at the column level.

Column Level:

Example: CREATE TABLE TEST3(STID INT NOT NULL, SNAME VARCHAR2(10) NOT NULL);

Testing:
INSERT INTO TEST3 VALUES(101, ‘A’); —-Allow
INSERT INTO TEST3 VALUES(101, ‘A’); —Allow
INSERT INTO TEST3 VALUES(NULL, NULL); —-Not Allow

Real-time Examples to Understand NOT NULL Constraint in Oracle:

Let us understand NOT NULL Constraint in Oracle with one real-time example. To understand this concept, first, let us create the Student table by executing the following SQL Script.

CREATE TABLE Student (
  StudentId INT NOT NULL,
  Name VARCHAR2(20) NOT NULL,
  RegdNo VARCHAR2(10) NOT NULL,
  Branch VARCHAR2(10) NOT NULL,
  Mobile VARCHAR2(10)
);

As you can see, here, we created the Student table with five columns i.e. StudentId, Name, RegdNo, Branch, and Mobile. There is no possible data row, where the students StudentId, Name, RegdNo, Branch will have a NULL value or no value. So, we applied the NOT NULL Constraint to StudentId, Name, RegdNo, Branch columns. On the other hand, it may possible that the mobile number is not present and hence we need not apply the NOT NULL Constraint to the Mobile Number column.

Examples:

Now, try to execute the below two INSERT statements.

INSERT INTO Student VALUES (1, 'Anurag', 'abc1001', 'CSE', '1111111111');
INSERT INTO Student VALUES (2, 'Sambit', 'abc1002', 'CSE', NULL);

Once you execute the above two statements, then you will see that both the INSERT statements are executed successfully as shown in the below image.

Now, try to execute the following INSERT statement.

INSERT INTO Student VALUES (1, 'Anurag', 'abc1001', 'CSE', '1111111111');

The above INSERT statement is also executed successfully as shown in the below image. Here, we have duplicate data in Id, Name, RegdNo, and Branch columns but then also it is inserted. This is because the NOT NULL constraint allows NULL values.

Now try to execute the below INSERT statement and this time the INSERT statement failed. This is because we are trying to INSERT NULL value in the Name column on which the NOT NULL constraint is applied.

INSERT INTO Student VALUES (4, NULL, 'abc1002', 'CSE', '2222222222');

When you execute the above INSERT statement, you will get the error ORA-01400: cannot insert NULL into (“SYSTEM”.”STUDENT”.”NAME”) as shown in the below image. This clearly tells us you can not insert a null value into the Name column of the Student table.

When to use NOT NULL Constraint?

If you don’t want to accept NULL values but you want to accept duplicates values to be stored in a column, then you need to apply NOT NULL Constraint to that column in Oracle.

Note: When we INSERT a null value into a column on which the NOT NULL constraint is imposed. The execution of the insert statement is terminated by displaying a user-friendly message telling the reason for termination and also specifying the database, the table, and the column name where the problem got occurred.

In the next article, I am going to discuss Check Constraint in Oracle with Examples. Here, in this article, I try to explain NOT NULL Constraint in Oracle with Examples and I hope you enjoy this NOT NULL Constraint in Oracle with Examples article.