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 is already NOT NULL, you don't have to add it again by modifying 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 definition.

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 disable. Apparently, the solution is to enable the constraint.

SQL> alter table employees modify constraint SYS_C007818 enable;

Table altered.

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 definition 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)

To know more about NOT NULL constraint, you may refer to: How to Add or Drop NOT NULL Constraint.

Leave a Reply

Your email address will not be published. Required fields are marked *