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