G-3170: Always use DEFAULT ON NULL declarations to assign default values to table columns if you refuse to store NULL values.
Unsupported in db* CODECOP Validators
We cannot identify column default values. Requires create table
and alter table
parser support or access to the Oracle Data Dictionary.
Restriction
Oracle Database 12c
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 (bad)
| 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'); -- NOSONAR: G-1050 literal is ok for a standalone insert
insert into null_test(test_case,column_defaulted) values (2,default); -- NOSONAR: G-1050 literal is ok for a standalone insert
insert into null_test(test_case,column_defaulted) values (3,null); -- NOSONAR: G-1050 literal is ok for a standalone insert
select test_case,column_defaulted from null_test;
|
| TEST_CASE COLUMN_DEF
--------- -----------
1 Value
2 Default
3
|
Example (good)
| 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'); -- NOSONAR: G-1050 literal is ok for a standalone insert
insert into null_test(test_case,column_defaulted) values (2,default); -- NOSONAR: G-1050 literal is ok for a standalone insert
insert into null_test(test_case,column_defaulted) values (3,null); -- NOSONAR: G-1050 literal is ok for a standalone insert
select test_case,column_defaulted from null_test;
|
| TEST_CASE COLUMN_DEF
---------- ----------
1 Value
2 Default
3 Default
|