Skip to content

How to Resolve ORA-01442: column to be modified to NOT NULL is already NOT NULL

  • by

ORA-01442

ORA-01442 means that the target column of table is already NOT NULL, so you don't have to add NOT NULL on the column. I know sometimes it might be hard to believe, but it's true.

Let's see an example.

SQL> conn hr/hr
Connected.

We checked the target column (highlighted text below) is no NOT NULL.

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                         VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

Since there's no NOT NULL on target column, so theoretically, we can add a NOT NULL constraint on it.

SQL>  alter table employees modify (first_name not null);
 alter table employees modify (first_name not null)
                               *
ERROR at line 1:
ORA-01442: column to be modified to NOT NULL is already NOT NULL

We got ORA-01442. This is because the NOT NULL constraint of the column is DISABLED by someone, so we can't see it in table description (definition) above.

Solution

Let's check the constraint status.

SQL> column constraint_name format a20;
SQL> select a.constraint_name, b.status from user_cons_columns a inner join user_constraints b on a.constraint_name = b.constraint_name where a.table_name = 'EMPLOYEES' and a.column_name = 'FIRST_NAME' and b.constraint_type = 'C' and search_condition_vc like '%NOT NULL';

CONSTRAINT_NAME      STATUS
-------------------- --------
SYS_C007818          DISABLED

It's DISABLED.

The second error pattern is that the constraint is enabled, but it has not validated.

SQL> select status, validated from dba_constraints where constraint_name = 'SYS_C007818';

STATUS   VALIDATED
-------- -------------
ENABLED  NOT VALIDATED

Apparently, the solution to ORA-01442 is to enable the constraint.

SQL> alter table employees modify constraint SYS_C007818 enable;

Table altered.

Or this:

SQL> alter table employees enable constraint SYS_C007818;

Table altered.

By default, the constraint is enabled and validated.

If enabling NOT NULL failed due to existing null value, you can just update null values into empty, blank or default values. A better practice is to provide a default value on NOT NULL column just like I said in How to Resolve ORA-01400: cannot insert NULL into.

Let's check the table description again.

SQL> desc employees
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 EMPLOYEE_ID                               NOT NULL NUMBER(6)
 FIRST_NAME                                NOT NULL VARCHAR2(20)
 LAST_NAME                                 NOT NULL VARCHAR2(25)
 EMAIL                                     NOT NULL VARCHAR2(25)
 PHONE_NUMBER                                       VARCHAR2(20)
 HIRE_DATE                                 NOT NULL DATE
 JOB_ID                                    NOT NULL VARCHAR2(10)
 SALARY                                             NUMBER(8,2)
 COMMISSION_PCT                                     NUMBER(2,2)
 MANAGER_ID                                         NUMBER(6)
 DEPARTMENT_ID                                      NUMBER(4)

An alternative solution to ORA-01442 is to drop NOT NULL constraint, then execute your adding NOT NULL statement again.

Leave a Reply

Your email address will not be published.