Oracle NOT NULL Constraint

NOT NULL Constraint is used in Oracle for specifying that a column cannot contain NULL Values.

This is an inline constraint that’s mentioned adjacent to the column name and its data type when creating a new table.

In this tutorial, we will learn how to add and drop NOT NULL constraint.

Add NOT NULL constraint using CREATE TABLE statement

The syntax for adding NOT NULL constraint using CREATE TABLE Statement is as follows:

Syntax

CREATE TABLE table_name (
    ...
    column1 datatype NOT NULL,
    ...
);

Here,

table_name – It is the name of table that we want to create.

column1 – It is the name of column on which we will apply NOT NULL constraint.

datatype – It is the datatype of column that we are creating with table.

Example:

Here, we will create a new table named suppliers and add NOT NULL constraint to some of its columns:

CREATE TABLE suppliers (
    supplier_id NUMBER(10) NOT NULL,
    supplier_name VARCHAR2(50) NOT NULL,
    product_name VARCHAR2(50)
    CONSTRAINT pk_supplier PRIMARY KEY (supplier_id)
);

Here, we created a new table named suppliers and added NOT NULL constraint to supplier_id and supplier_name columns. We also created and added a Primary Key constraint to supplier_id column to ensure that values in it are not just non-null but also unique.

Add NOT NULL Constraint using ALTER TABLE statement

The syntax for adding NOT NULL constraint in a table that already exists using ALTER TABLE statement is as follows:

Syntax

ALTER TABLE table_name MODIFY ( column_name NOT NULL);

Here,

table_name – It is the name of table in which we want to modify a column and add NOT NULL constraint.

column_name – Name of column on which NOT NULL constraint will be applied

Example:

Let’s try to add NOT NULL constraint in suppliers table using ALTER TABLE statement:

ALTER TABLE suppliers MODIFY ( supplier_id NOT NULL);

We won’t be able to add NULL values in supplier_id column from now on.

If the supplier_id column already contains NULL values then Oracle will return an error while applying NOT NULL constraint.

SQL Error: ORA-02296: cannot enable (OT.) - null values found

To fix this error, either eliminate all existing NULL values from column or update them to a non-null value like 0 using ALTER TABLE statement.

Drop Not NULL Constraint

The syntax for dropping NOT NULL Constraint is as follows:

Syntax

ALTER TABLE table_name MODIFY ( column_name NULL)

Here,

table_name – Name of table from which we want to drop NOT NULL constraint.

column_name – Name of column from which NOT NULL constraint will be dropped

Example:

Let’s modify a column from suppliers table to accept NULL values:

ALTER TABLE suppliers
MODIFY ( supplier_id NULL);

We can now add NULL values in supplier_id column after executing this query.

In this tutorial, we learned how to use NOT NULL constraint to prevent columns from accepting NULL values in a table.