Database Constraints Enforced but not Validated for New Data Now, but Not Existing Data

Recently, while discussing a problem, somebody said to me 'I would like to make this column NOT NULL to stop [this problem] from occurring, but first I would need to go back and fix all the historical data'. 

In Oracle, a constraint that is enabled will apply during DML, so as you insert or update the data, the constraint is applied to the rows that are being updated. Only when a constraint is validated, does the database check that all the data in the table conforms to the constraint.  

See also Oracle 19c SQL Language Reference: Common SQL DDL Clauses: constraint enable clause.

If you create a constraint that is enforced, but not validated, you may be able to prevent a problem from getting worse while you fix the data you already have.

A Demonstration 

I will create a table with a unique constraint and two other columns that are NOT NULL.  I put some data in the tables.  Column B is null for some of the rows, but not for others.

create table t (a number, b number, c number, constraint t_pk primary key(A));

insert into t (a,b)
select level, CASE WHEN MOD(level,2)=1 then 1 end --B is null on alternate rows
from dual connect by level<=10;

select * from t;

         A          B          C
---------- ---------- ----------
         1          1           
         2                      
         3          1           
         4                      
         5          1           
         6                      
         7          1           
         8                      
         9          1           
        10                      

10 rows selected.

I would like to make B a NOT NULL column (to stop the application from writing an invalid value to the database), but cannot because I already have some invalid values in the database.

SQL> alter table t modify b not null ;

ORA-02296: cannot enable (SCOTT.) - null values found
02296. 00000 - "cannot enable (%s.%s) - null values found"
*Cause:    an alter table enable constraint failed because the table
           contains values that do not satisfy the constraint.
*Action:   Obvious

However, I can create the constraint with the NOVALIDATE option.

SQL> alter table t modify b not null novalidate;

Table T altered.

SQL> select constraint_name, search_condition_vc from user_constraints where table_name = 'T';

CONSTRAINT_NAME SEARCH_CONDITION_VC                                         
--------------- ------------------------------------------------------------
T_PK                                                                        
SYS_C00221684   "B" IS NOT NULL

Note that at the moment column B is not described as NOT NULL because although the constraint is enforced, it has not been validated.

SQL> desc t
Name Null?    Type   
---- -------- ------ 
A    NOT NULL NUMBER 
B             NUMBER 
C             NUMBER

If I try to add more rows where some of the data in column B is null, the constraint prevents it.

SQL> insert into t (a,b)
  2  select level+10, CASE WHEN MOD(level,2)=1 then 1 end 
  3  from dual connect by level<=10;

ORA-01400: cannot insert NULL into ("SCOTT"."T"."B")

I can set B column to a NOT NULL value, but I cannot set it back to null.

SQL> update t set b = 2 where a=2;

1 row updated.

SQL> update t set b = NULL where a=2;

ORA-01407: cannot update ("SCOTT"."T"."B") to NULL

I can successfully update a different column on a row where B is null and therefore does not meet the constraint.  I do not get an error because I have not updated it.  

SQL> SQL> update t set c = a;

10 rows updated.

Eventually, I will want to validate the constraint so that I know that B has a not null value in all the rows. But I can't do it while there are still some null values

SQL> select * from t;

         A          B          C
---------- ---------- ----------
         1          1          1
         2          2          2
         3          1          3
         4                     4
         5          1          5
         6                     6
         7          1          7
         8                     8
         9          1          9
        10                    10

SQL> DECLARE
  2    l_sql CLOB;
  3  BEGIN
  4    FOR I IN (select * from user_constraints where table_name = 'T' AND constraint_type ='C' 
  5              and validated != 'VALIDATED' and search_condition_vc = '"B" IS NOT NULL') LOOP
  6      l_sql := 'alter table '||i.table_name||' modify constraint '||i.constraint_name||' VALIDATE';
  7      dbms_output.put_line(l_sql);
  8      EXECUTE IMMEDIATE l_sql;
  9    END LOOP;
 10  END;
 11  /
alter table T modify constraint SYS_C00221684 VALIDATE

ORA-02293: cannot validate (SCOTT.SYS_C00221684) - check constraint violated
ORA-06512: at line 7
ORA-06512: at line 7
02293. 00000 - "cannot validate (%s.%s) - check constraint violated"
*Cause:    an alter table operation tried to validate a check constraint to
           populated table that had nocomplying values.
*Action:   Obvious

First I have to fix the data, and then I can validate the constraint

SQL> UPDATE t set b=a where b is null;

4 rows updated.

SQL> REM now validate the constraint
SQL> BEGIN
  2    FOR I IN (select * from user_constraints where table_name = 'T' AND constraint_type ='C' 
  3              and validated != 'VALIDATED' and search_condition_vc = '"B" IS NOT NULL') LOOP
  4      EXECUTE IMMEDIATE 'alter table '||i.table_name||' modify constraint '||i.constraint_name||' VALIDATE';
  5    END LOOP;
  6  END;
  7  /

PL/SQL procedure successfully completed.

Only now that the new constraint has been validated is the column described as NOT NULL.

SQL> desc t
Name Null?    Type   
---- -------- ------ 
A    NOT NULL NUMBER 
B    NOT NULL NUMBER 
C             NUMBER