The following shows how to add in a table a new column that is NOT NULL when a table already has rows without providing a default value.

SQL> create table scott.testn ( x number not null);

Table created.

SQL> insert into scott.testn values ( 11 );

1 row created.

SQL> commit;

Commit complete.

-- First step add new column without the NULL constraint
SQL> alter table scott.testn add nn number;

Table altered.

-- Then modify the column to be NOT NULL using NOVALIDATE
SQL> alter table scott.testn modify nn not null novalidate;

Table altered.

-- Shows the table doesn't show the NOT NULL constraint on the column NN yet
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– ———————
X NOT NULL NUMBER
NN NUMBER

-- As shown above even though not shown the NOT NULL constraint is there as new values cannot be inserted in the table that are NULL
SQL> insert into scott.testn values (12, null);
insert into scott.testn values (12, null)
*
ERROR at line 1:
ORA-01400: cannot insert NULL into (“SCOTT”.”TESTN”.”NN”)

-- Now we can enable the constraint by updating the rows that have NULL value so we need to find the constraint name
SQL> select column_name, constraint_name from dba_cons_columns where table_name = 'TESTN' and owner = 'SCOTT';

COLUMN_NAME CONSTRAINT_NAME
——————– ——————————
X SYS_C005261
NN SYS_C005262

-- Rows update
SQL> update scott.testn set nn = 1 where nn is null;

1 row updated.

SQL> commit;

Commit complete.

--
SQL> alter table scott.testn modify constraint SYS_C005262 validate;

Table altered.

-- Shows the NOT NULL constraint on the column NN
SQL> desc scott.testn;
Name Null? Type
—————————————– ——– —————————-
X NOT NULL NUMBER
NN NOT NULL NUMBER

-- shows that one can't add a new column not null as table is not empty
SQL> alter table scott.testn add mm number not null;
alter table scott.testn add mm number not null
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column

-- shows it can't add a new column not null with novalidate as table is not empty
SQL> alter table scott.testn add mm number not null novalidate;
alter table scott.testn add mm number not null novalidate
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column