Oracle: ORA-01451 Error Message

ORA-01451 Error Message

Learn the cause and how to resolve the ORA-01451 error message in Oracle.

Description

When you encounter an ORA-01451 error, the following error message will appear:

• ORA-01451: column to be modified to NULL cannot be modified to NULL

Cause

You tried to execute a ALTER TABLE MODIFY attempting to change a column to allow NULL values, but the column either already allows NULL values or the column is used in a primary key or check constraint and can not accept NULL values.

Resolution

The option(s) to resolve this Oracle error are:

Option #1

Drop the primary key or check constraint index that is preventing you for changing the column to allow NULL values.

Option #2

The column already allows NULL values and no action is required.

Scenario #1

For example, if you had a table called suppliers defined as follows:

CREATE TABLE suppliers
( supplier_name varchar2(50) NOT NULL,
  city varchar2(35),
  state varchar2(2),
  zip varchar2(10),
  CONSTRAINT suppliers_pk PRIMARY KEY (supplier_name)
);

Then executed the following ALTER TABLE statement

ALTER TABLE suppliers
 MODIFY supplier_name NULL;

You would receive the following error message:

You could correct the error by removing the primary key from the suppliers table:

ALTER TABLE suppliers
 DROP CONSTRAINT suppliers_pk;

Now, the supplier_name field will be defined as allowing NULL values.

Scenario #2

For example, if you had a table called suppliers defined as follows:

CREATE TABLE suppliers
( supplier_name varchar2(50),
  city varchar2(35),
  state varchar2(2),
  zip varchar2(10)
);

Then executed the following ALTER TABLE statement

ALTER TABLE suppliers
 MODIFY supplier_name NULL;

You would receive the following error message:

This error is encountered because you've already defined the supplier_name column as allowing NULL values, so there is no action to be taken.