DEFAULT ON NULL, and ORA-01451: column to be modified to NULL cannot be modified to NULL

If you wish to remove a NOT NULL constraint from a column, normally you would execute this:

alter table t modify module null;

The other day a colleague trying to execute this on one of our tables encountered this error instead:

ORA-01451: column to be modified to NULL cannot be modified to NULL
*Cause:    the column may already allow NULL values, the NOT NULL constraint
           is part of a primary key or check constraint.
*Action:   if a primary key or check constraint is enforcing the NOT NULL
           constraint, then drop that constraint.

Most of the time when you see this error, it will be because of a primary key constraint on the column. This wasn’t the case for my colleague, however.

This particular column had a NOT NULL constraint. This constraint was not added deliberately by us; it had been applied automatically because the column has a default expression using the DEFAULT ON NULL option. For example:

create table t (
    ...
    module varchar2(64) default on null sys_context('userenv','module'),
    ...
);

A column defined with the DEFAULT ON NULL option means that if anything tries to insert a row where the column is null, or not included in the insert statement, the default expression will be used to set the column’s value. This is very convenient in cases where we always want the default value applied, even if some code tries to insert NULL into that column.

One would normally expect that a DEFAULT ON NULL implies that the column will never be NULL, so it makes sense that Oracle would automatically add a NOT NULL constraint on the column.

An edge case where this assumption does not hold true is when the default expression may itself evaluate to NULL; when that occurs, the insert will fail with ORA-01400: cannot insert NULL into ("SAMPLE"."T"."MODULE").

Therefore, my colleague wanted to remove the NOT NULL constraint, but their attempt failed with the ORA-01451 exception noted at the start of this article.

Unfortunately for us, the DEFAULT ON NULL option is not compatible with allowing NULLs for the column; so we had to remove the DEFAULT ON NULL option. If necessary, we could add a trigger on the table to set the column’s value if the inserted value is null.

The way to remove the DEFAULT ON NULL option is to simply re-apply the default, omitting the ON NULL option, e.g.:

alter table t modify module default sys_context('userenv','module');

Here’s a transcript illustrating the problem and its solution:

create table t (
    dummy number,
    module varchar2(64) default on null sys_context('userenv','module')
);

Table T created.

exec dbms_application_info.set_module('SQL Developer',null);

insert into t (dummy) values (1);

1 row inserted.

select * from t;

     DUMMY MODULE                                                     
---------- -----------------------------------------------------------
         1 SQL Developer




exec dbms_application_info.set_module(null,null);

insert into t (dummy) values (2);

Error report -
ORA-01400: cannot insert NULL into ("SAMPLE"."T"."MODULE")




alter table t modify module null;

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




alter table t modify module default sys_context('userenv','module');

Table T altered.

insert into t (dummy) values (3);

1 row inserted.

select * from t;

     DUMMY MODULE                                                     
---------- -----------------------------------------------------------
         1 SQL Developer                                             
         3