Oracle 23c - SQL

Enforcing Default Values when Updating Columns to NULL (23c)

/ Oracle DB 23c New Features, SQL 23c / By

Oracle Database 23c introduces a powerful new feature that empowers developers and DBAs to maintain data integrity and simplify application logic: the ability to define columns as DEFAULT ON NULL for update operations. This enhancement builds upon the existing DEFAULT ON NULL functionality for insert operations, extending its benefits to update scenarios as well.

Pre-23c Workarounds:

  • Triggers: Prior to Oracle 23c, enforcing default values on updates often involved creating triggers to intercept update attempts and set default values if NULL was specified. This approach could add complexity and overhead to database operations.
  • Application Logic: Developers often had to incorporate conditional logic within their application code to handle default values during updates, making code more intricate and potentially error-prone.

Oracle 23c Simplifies the Process:

Declarative Syntax: The new DEFAULT ON NULL for update feature offers a declarative approach to defining default behavior for updates. Simply add the “DEFAULT ON NULL FOR INSERT AND UPDATE” clause to column definitions during table creation or alteration. Here is an example:

CREATE TABLE employees (
    employee_id NUMBER,
    email VARCHAR2(50),
    hire_date DATE DEFAULT ON NULL FOR INSERT AND UPDATE SYSDATE 
);


-- insert an employee hired on 1/1/2024
SQL> INSERT INTO employees (employee_id, email, hire_date) VALUES (1, 'john@example.com', TO_DATE('01-10-2024','DD-MM-YY'));
1 row inserted.

-- set his hire date to NULL
SQL> UPDATE employees SET hire_date=NULL WHERE employee_id=1;
1 row updated.

-- query the hire date, it is set to the default value
SQL> SELECT hire_date FROM employees WHERE employee_id=1;

HIRE_DATE
____________
20-DEC-23

If you specify DEFAULT ON NULL FOR INSERT ONLY, it is equivalent to DEFAULT ON NULL. Yet, we cannot update the column to a NULL value. Here is a demonstration of this concept (the output is truncated a little bit for readability): 

SQL> CREATE TABLE employees (
  2      employee_id NUMBER,
  3      email VARCHAR2(50),
  4      hire_date DATE DEFAULT ON NULL SYSDATE
  5* );

Table EMPLOYEES created.

SQL> INSERT INTO employees (employee_id, email, hire_date) VALUES (1, 'john@example.com', NULL);
1 row inserted.

SQL> SELECT HIRE_DATE FROM EMPLOYEES WHERE EMPLOYEE_ID=1;

HIRE_DATE
____________
20-DEC-23

SQL> UPDATE employees SET hire_date=NULL WHERE employee_id=1;

SQL Error: ORA-01407: cannot update ("USER1"."EMPLOYEES"."HIRE_DATE") to NULL
01407. 00000 -  "cannot update (%s) to NULL"

I am not sure the point behind this behavior, but this is something that need to keep in mind when using this feature.

Scroll to Top