DBA Hub

📋Steps in this guide1/4

DEFAULT ON NULL FOR INSERT AND UPDATE in Oracle Database 23ai/26ai

In Oracle 23ai/26ai 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.

oracle 23configurationintermediate
by OracleDba
21 views
1

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. We insert two rows. The first explicitly references the description column and assigns a null value. The second omits the description column. 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.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
drop table if exists t1 purge;

create table t1 (
  id           number,
  description  varchar2(15) default 'banana'
);

insert into t1 (id, description) values (1, null);
insert into t1 (id) values (2);

select * from t1;

        ID DESCRIPTION
---------- ---------------
         1
         2 banana

SQL>

update t1
set    description = null;


select * from t1;

        ID DESCRIPTION
---------- ---------------
         1
         2

SQL>
2

DEFAULT ON NULL [FOR INSERT ONLY]

In Oracle 12c we got the ability to define a column as , allowing it to be assigned a default value even if it was explicitly assigned a null value in an insert statement. The form was added in 23ai/26ai to allow us to explicitly state insert-only, but it is functionally identical to . To demonstrate this we create a table with a default value on two of the columns using the two variations of the syntax. We insert two rows. The first explicitly references the description columns and assigns a null value. The second omits the description columns. This time the default value was applied whether the description columns were omitted or explicitly set to a null value. Remember, makes a column mandatory, so we can't set the values to null using an update statement.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
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'
);

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>

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

DEFAULT ON NULL FOR INSERT AND UPDATE

In Oracle 23ai/26ai we have the ability to define a column as , 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. We insert two rows. The first explicitly references the description column and assigns a null value. The second omits the description column. 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. This time we update the descriptions using an explicit null value. Notice this time the update didn't error, and the default values were assigned in place of the explicit null values.

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
drop table if exists t1 purge;

create table t1 (
  id           number,
  description  varchar2(15) default on null for insert and update 'banana'
);

insert into t1 (id, description) values (1, null);
insert into t1 (id) values (2);

select * from t1;

        ID DESCRIPTION
---------- ---------------
         1 banana
         2 banana

SQL>

update t1
set    description = 'apple';


select * from t1;

        ID DESCRIPTION
---------- ---------------
         1 apple
         2 apple

SQL>

update t1
set    description = null;


select * from t1;

        ID DESCRIPTION
---------- ---------------
         1 banana
         2 banana

SQL>
4

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. We have now overridden the processing, so both inserts and updates fail. Remember to drop that trigger. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
create or replace trigger t1_trg 
before insert or update on t1
for each row
begin
  :new.description := NULL;
end;
/

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>

drop trigger if exists t1_trg;

Comments (0)

Please to add comments

No comments yet. Be the first to comment!