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.