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.

Introduction to Oracle NOT NULL constraint

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)

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