Today I learned something new about the DEFAULT ON NULL clause.
Since Oracle database release 12 it is possible to define a DEFAULT ON NULL clause which is used when a NULL is provided as value. The thing I didn't know is that the column is automatically defined as NOT NULL.
Is this documented behaviour? Yes, it is. Quote from the documentation:
When you specify ON NULL, the NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified.
SQL> select banner 2 from v$version 3 / BANNER -------------------------------------------------------------------------------- Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
SQL> create table t 2 (col1 varchar2(10) default on null 'hello') 3 / Table T created.
SQL> desc t Name Null? Type ---- -------- ------------ COL1 NOT NULL VARCHAR2(10)
And in case you are wondering if you can change the column afterwards to NULL, the answer is "no".
SQL> alter table t 2 modify (col1 null) 3 / Error starting at line : 1 in command - alter table t modify (col1 null) Error report - ORA-01451: column to be modified to NULL cannot be modified to NULL 01451. 00000 - "column to be modified to NULL cannot be modified to NULL" *Cause: the column may already allow NULL values, the NOT NULL constraint is part of a primary key or check constraint. *Action: if a primary key or check constraint is enforcing the NOT NULL constraint, then drop that constraint.