Oracle 23c/23ai: DEFAULT ON NULL in Action

Understanding Legacy, Insert-Only, and Insert-and-Update Defaults

Oracle 23c introduced a subtle but powerful enhancement to column defaults: the ability to decide when a default value should automatically replace a NULL.

Until now, the DEFAULT clause worked only when the column was omitted in an INSERT. With 23c, we can tell Oracle to also apply defaults when you explicitly insert NULL, and even when you update a column to NULL.

---

Create the test table

DROP TABLE test_tbl;

CREATE TABLE test_tbl 
(
  test_name VARCHAR2(50),

  -- Classic default – fires only when column is omitted
  col1 VARCHAR2(50) DEFAULT 'LEGACY',

  -- Fires when column value is NULL on insert
  col2 VARCHAR2(50) DEFAULT ON NULL 'ON NULL',

  -- Fires only on INSERT when NULL
  col3 VARCHAR2(50) DEFAULT ON NULL FOR INSERT ONLY 'ON NULL FOR INSERT ONLY',

  -- Fires on both INSERT and UPDATE when NULL
  col4 VARCHAR2(50) DEFAULT ON NULL FOR INSERT AND UPDATE 'ON NULL FOR INSERT AND UPDATE'
);

Describe the table:

Name      Null?    Type         
--------- -------- ------------ 
TEST_NAME          VARCHAR2(50) 
COL1               VARCHAR2(50) 
COL2      NOT NULL VARCHAR2(50) 
COL3      NOT NULL VARCHAR2(50) 
COL4      NOT NULL VARCHAR2(50) 

• Notice that Oracle marks all DEFAULT ON NULL columns as NOT NULL — the engine enforces that a persistent NULL cannot exist there.

---

Insert a row with only first column

INSERT INTO test_tbl (test_name)
VALUES ('insert');

SELECT * FROM test_tbl;

TEST_NAME	COL1	COL2	COL3	COL4
insert	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE

• All default values applied because columns were omitted.

---

Insert explicitly passing NULLs

INSERT INTO test_tbl
(test_name, col1, col2, col3, col4)
VALUES ('insert', NULL, NULL, NULL, NULL);

SELECT * FROM test_tbl;

TEST_NAME	COL1	COL2	COL3	COL4
insert	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert	(NULL)	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE

• col1 (legacy default) stays NULL when NULL is explicitly inserted.
• col2, col3, and col4 substitute their defaults automatically.

---

Insert a third row to test updates

INSERT INTO test_tbl (test_name)
VALUES ('insert then update');

SELECT * FROM test_tbl;

TEST_NAME	COL1	COL2	COL3	COL4
insert	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert	(NULL)	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert then update	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE

---

Try to update to NULL

UPDATE test_tbl
   SET col2 = NULL
 WHERE test_name = 'insert then update';

ORA-01407: cannot update ("TOMASZ"."TEST_TBL"."COL2") to NULL

Same for col3:

UPDATE test_tbl
   SET col3 = NULL
 WHERE test_name = 'insert then update';

ORA-01407: cannot update ("TOMASZ"."TEST_TBL"."COL3") to NULL

Both fail because:

• col2 = DEFAULT ON NULL → substitution only on INSERT.
• col3 = DEFAULT ON NULL FOR INSERT ONLY → same restriction.

Oracle enforces NOT NULL semantics, so an explicit update to NULL triggers ORA-01407.

---

Check table content again

SELECT * FROM test_tbl;

TEST_NAME	COL1	COL2	COL3	COL4
insert	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert	(NULL)	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert then update	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE

---

Update columns that allow it

UPDATE test_tbl
   SET col1 = NULL, col4 = NULL
 WHERE test_name = 'insert then update';

SELECT * FROM test_tbl;

TEST_NAME	COL1	COL2	COL3	COL4
insert	LEGACY	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert	(NULL)	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE
insert then update	(NULL)	ON NULL	ON NULL FOR INSERT ONLY	ON NULL FOR INSERT AND UPDATE

• col1 became NULL (legacy behavior).
• col4 was set to its default again, because it’s DEFAULT ON NULL FOR INSERT AND UPDATE.

---

Behavior Matrix

Column	Definition	Insert (omitted)	Insert (NULL)	Update (NULL)	NOT NULL enforced
col1	DEFAULT ‘LEGACY’	Default applies	Stores NULL	Stores NULL	❌
col2	DEFAULT ON NULL ‘ON NULL’	Default applies	Default applies	ORA-01407	✅
col3	DEFAULT ON NULL FOR INSERT ONLY …	Default applies	Default applies	ORA-01407	✅
col4	DEFAULT ON NULL FOR INSERT AND UPDATE …	Default applies	Default applies	Default applies	✅

---

Have fun

  Tomasz