Default Values for Columns on Explicit NULL Insertion Oracle Database 12C release 1 (12.1)

DEFAULT ON NULL in Oracle 12c  assign default value if INSERT attempts to assign a value that evaluates to NULL.

  • DEFAULT ON NULL <VALUE>
drop table test_tbl;

create table test_tbl
( id  number default on null 5,
  id1 number);

insert into test_tbl values(null, 10);
insert into test_tbl values(100, 20);

select * from test_tbl;

        ID        ID1
---------- ----------
         5         10 
       100         20

To remember

  • NOT NULL constraint and NOT DEFERRABLE constraint state are implicitly specified when DEFAULT NOT NULL is used
declare
  v_id number;
begin  
 v_id := dbms_metadata.session_transform;

 dbms_metadata.set_transform_param(v_id, 'STORAGE', FALSE);
 dbms_metadata.set_transform_param(v_id, 'SEGMENT_ATTRIBUTES', FALSE);
end;
/

select dbms_metadata.get_ddl('TABLE', 'TEST_TBL') from dual;

CREATE TABLE "TOMASZ"."TEST_TBL" 
( "ID" NUMBER DEFAULT 5 NOT NULL ENABLE, 
  "ID1" NUMBER
)

select constraint_name, constraint_type, deferrable, search_condition 
from user_constraints
where table_name='TEST_TBL';

CONSTRAINT_NAME CONSTRAINT_TYPE DEFERRABLE     SEARCH_CONDITION
--------------- --------------- -------------- ----------------
SYS_C0010162    C               NOT DEFERRABLE "ID" IS NOT NULL

Have a fun 🙂

Tomasz

Leave a Reply

Your email address will not be published. Required fields are marked *