G-3170
🆓Error
Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.
Reason
Default values have been nullifiable until Oracle Database 12c. Meaning any tool sending null as a value for a column having a default value bypassed the default value. Starting with Oracle Database 12c default definitions may have an on null definition in addition, which will assign the default value in case of a null value too.
Example
Non-Compliant Example
create table null_test ( test_case number(2) not null ,column_defaulted varchar2(10 char) default 'Default') / insert into null_test(test_case,column_defaulted) values (1,'Value'); insert into null_test(test_case,column_defaulted) values (2,default); insert into null_test(test_case,column_defaulted) values (3,null); select test_case,column_defaulted from null_test;
Issues
| Line | Column | Message |
|---|---|---|
| 3 | 39 | Missing DEFAULT ON NULL for column_defaulted. |
Explanation
TEST_CASE COLUMN_DEF
--------- -----------
1 Value
2 Default
3 Compliant Solution - ★★★★★
create table null_test ( test_case number(2) not null ,column_defaulted varchar2(10 char) default on null 'Default') / insert into null_test(test_case,column_defaulted) values (1,'Value'); insert into null_test(test_case,column_defaulted) values (2,default); insert into null_test(test_case,column_defaulted) values (3,null); select test_case,column_defaulted from null_test;
Explanation
TEST_CASE COLUMN_DEF
---------- ----------
1 Value
2 Default
3 DefaultReferences
- same as Trivadis G-3170