Oracle NOT NULL

Summary: in this tutorial, you will learn how to use the Oracle NOT NULL constraint to enforce a column not to accept NULL values.

An Oracle NOT NULL constraint specifies that a column cannot contain NULL values. The Oracle NOT NULL constraints are inline constraints which are typically used in the column definition of the CREATE TABLE statement.

CREATE TABLE table_name ( ... column_name data_type NOT NULL ... );
Code language: SQL (Structured Query Language) (sql)

It is possible to add a NOT NULL constraint to an existing table by using the ALTER TABLE statement.

ALTER TABLE table_name MODIFY ( column_name NOT NULL);
Code language: SQL (Structured Query Language) (sql)

In this case, the column_name must not contain any NULL value before applying the NOT NULL constraint.

Oracle NOT NULL constraint examples

The following statement creates the surcharges table:

CREATE TABLE surcharges ( surcharge_id NUMBER GENERATED BY DEFAULT AS IDENTITY, surcharge_name VARCHAR2(255) NOT NULL, amount NUMBER(9,2), PRIMARY KEY (surcharge_id) );
Code language: SQL (Structured Query Language) (sql)

The surcharges table has three columns: surcharge id, surcharge name, and amount.

The surcharge_id column is the primary key column of the table specified by the PRIMARY KEY constraint, therefore, Oracle implicitly adds a NOT NULL constraint to this column.

The surcharge_name column has a NOT NULL constraint specified explicitly in the column definition.

The amount column can accept NULL values.

The following statement inserts a row into the surcharges table:

INSERT INTO surcharges(surcharge_name, amount) VALUES('Late order placement',10);
Code language: SQL (Structured Query Language) (sql)

It works as expected.

However, the following statement does not work:

INSERT INTO surcharges(surcharge_name, amount) VALUES(null,20);
Code language: SQL (Structured Query Language) (sql)

Because it attempts to insert a NULL value into the surcharge column which has a NOT NULL constraint.

The following statement works because the amount column accepts NULL values:

INSERT INTO surcharges(surcharge_name, amount) VALUES('Rush Order',NULL);
Code language: SQL (Structured Query Language) (sql)

The following statement displays all constraints of the surcharges table:

SELECT table_name, constraint_name, search_condition FROM user_constraints WHERE table_name = 'SURCHARGES';
Code language: SQL (Structured Query Language) (sql)
Oracle NOT NULL - constraints

If you want to add a NOT NULL constraint to the amount column, you use the following ALTER TABLE statement:

ALTER TABLE surcharges MODIFY (amount NOT NULL);
Code language: SQL (Structured Query Language) (sql)

The following error occurred:

SQL Error: ORA-02296: cannot enable (OT.) - null values found
Code language: SQL (Structured Query Language) (sql)

Because the surcharges table contains a NULL value.

So before adding the NOT NULL constraint, you need to make sure that the existing data in the surcharges table does not violate the NOT NULL constraint:

UPDATE surcharges SET amount = 0 WHERE amount IS NULL;
Code language: SQL (Structured Query Language) (sql)

Now, if you execute the ALTER TABLE statement again:

ALTER TABLE surcharges MODIFY (amount NOT NULL);
Code language: SQL (Structured Query Language) (sql)

It should work as expected.

Drop NOT NULL constraints

Sometimes, you need to change a column with a NOT NULL constraint to accept NULL values.

To do this, you need to remove the NOT NULL constraint from the column by using the ALTER TABLE statement as below:

ALTER TABLE table_name MODIFY ( column_name NULL)
Code language: SQL (Structured Query Language) (sql)

For example, to drop the NOT NULL constraint from the amount column of the surcharges table, you use the following statement:

ALTER TABLE surcharges MODIFY (amount NULL);
Code language: SQL (Structured Query Language) (sql)

In this tutorial, you have learned how to use the Oracle NOT NULL constraint to enforce a column not to accept NULL values.

Was this tutorial helpful?