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.