ORA-01449: column contains NULL values; cannot alter to NOT NULL

ORA-01449: column contains NULL values; cannot alter to NOT NULL

Oracle PL/SQL error message: ORA-01449: column contains NULL values; cannot alter to NOT NULL.

Cause:

You execute an ALTER TABLE MODIFY statement attempted to change the definition of a column containing NULL values to NOT NULL.

Solution:

Set all NULL values in the column to values other than NULL, then re-execute the statement.




Example:

CREATE TABLE TEST3(
ID NUMBER NOT NULL, 
NAME VARCHAR2(250)
) ;
Insert into TEST3 (ID, NAME) values (1,'STRING 1');
Insert into TEST3 (ID, NAME) values (2,'STRING 2');
Insert into TEST3 (ID, NAME) values (3, null);

ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID, NAME);  

Output:

table TEST3 created.
1 rows inserted.
1 rows inserted.
1 rows inserted.
ORA-01449: column contains NULL values; cannot alter to NOT NULL

Correct:

ALTER TABLE TEST3 ADD CONSTRAINT PK_TEST3 PRIMARY KEY (ID);  

Output:

table TEST3 altered.