8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 23c » Here

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.

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...

Back to the Top.