8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux
DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23c
In Oracle 23c we can define a column as DEFAULT ON NULL FOR INSERT AND UPDATE
. This replaces explicit null values with the default value in update statements.
Originally default values were only applied during insert operations if the column with the default value was not explicitly referenced in the insert statement. In Oracle 12c we got the ability to define a column as DEFAULT ON NULL
, allowing it to be assigned a default value even if it was explicitly assigned a null value in an insert statement. Oracle 23c brings similar default value processing to update operations.
- DEFAULT
- DEFAULT ON NULL [FOR INSERT ONLY]
- DEFAULT ON NULL FOR INSERT AND UPDATE
- Breaking Defaults with Triggers
Related articles.
DEFAULT
Originally default values were only applied during insert operations if the column with the default value was not explicitly referenced in the insert statement.
To demonstrate this we create a table with a default value on one of the columns.
drop table if exists t1 purge; create table t1 ( id number, description varchar2(15) default 'banana' );
We insert two rows. The first explictly references the description column and assigns a null value. The second omits the description column.
insert into t1 (id, description) values (1, null); insert into t1 (id) values (2); select * from t1; ID DESCRIPTION ---------- --------------- 1 2 banana SQL>
As expected, the default value was only applied when the description column was omitted from the insert statement.
If we update the description column to a null value, we see the default value is not applied.
update t1 set description = null; select * from t1; ID DESCRIPTION ---------- --------------- 1 2 SQL>
DEFAULT ON NULL [FOR INSERT ONLY]
In Oracle 12c we got the ability to define a column as DEFAULT ON NULL
, allowing it to be assigned a default value even if it was explicitly assigned a null value in an insert statement. The DEFAULT ON NULL FOR INSERT ONLY
form was added in 23c to allow us to explicitly state insert-only, but it is functionally identical to DEFAULT ON NULL
.
To demonstrate this we create a table with a default value on two of the columns using the two variations of the syntax.
drop table if exists t1 purge; create table t1 ( id number, description1 varchar2(15) default on null 'banana', description2 varchar2(15) default on null for insert only 'apple' );
We insert two rows. The first explictly references the description columns and assigns a null value. The second omits the description columns.
insert into t1 (id, description1, description2) values (1, null, null); insert into t1 (id) values (2); select * from t1; ID DESCRIPTION1 DESCRIPTION2 ---------- --------------- --------------- 1 banana apple 2 banana apple SQL>
This time the default value was applied whether the description columns were omitted or explicitly set to a null value.
Remember, DEFAULT ON NULL
makes a column mandatory, so we can't set the values to null using an update statement.
desc t1 Name Null? Type ----------------------------------------- -------- ---------------------------- ID NUMBER DESCRIPTION1 NOT NULL VARCHAR2(15) DESCRIPTION2 NOT NULL VARCHAR2(15) SQL> update t1 set description1 = null, description2 = null; set description1 = null, * ERROR at line 2: ORA-01407: cannot update ("TESTUSER1"."T1"."DESCRIPTION1") to NULL SQL>
DEFAULT ON NULL FOR INSERT AND UPDATE
In Oracle 23c we have the ability to define a column as DEFAULT ON NULL FOR INSERT AND UPDATE
, so the default value is applied during update operations if an explicit null value is assigned.
To demonstrate this we create a table with a default value on one of the columns.
drop table if exists t1 purge; create table t1 ( id number, description varchar2(15) default on null for insert and update 'banana' );
We insert two rows. The first explictly references the description column and assigns a null value. The second omits the description column.
insert into t1 (id, description) values (1, null); insert into t1 (id) values (2); select * from t1; ID DESCRIPTION ---------- --------------- 1 banana 2 banana SQL>
As expected, the default value was applied whether the description column was omitted or explicitly set to a null value.
We update the descriptions to a different value.
update t1 set description = 'apple'; select * from t1; ID DESCRIPTION ---------- --------------- 1 apple 2 apple SQL>
This time we update the descriptions using an explicit null value.
update t1 set description = null; select * from t1; ID DESCRIPTION ---------- --------------- 1 banana 2 banana SQL>
Notice this time the update didn't error, and the default values were assigned in place of the explicit null values.
Breaking Defaults with Triggers
It's possible to break this default value functionality using triggers. In this example we create a trigger to set the description column to a null value on insert or update.
create or replace trigger t1_trg before insert or update on t1 for each row begin :new.description := NULL; end; /
We have now overridden the DEFAULT ON NULL
processing, so both inserts and updates fail.
insert into t1 (id, description) values (3, null); * ERROR at line 1: ORA-01400: cannot insert NULL into ("TESTUSER1"."T1"."DESCRIPTION") SQL> update t1 set description = null; * ERROR at line 2: ORA-01407: cannot update ("TESTUSER1"."T1"."DESCRIPTION") to NULL SQL>
Remember to drop that trigger.
drop trigger if exists t1_trg;
For more information see:
Hope this helps. Regards Tim...