Why Adding NOT NULL Columns Can Still Store NULLs in Oracle?

Problem Overview

Adding a column without a NOT NULL constraint but with a default value of NULL seems contradictory because a nullable column already defaults to NULL. In Oracle, however, a NOT NULL column can be added with a default of NULL. The default value is stored in the data‑dictionary table sys.ecol$ and is not back‑filled into existing rows.

Query Behavior Differences

1. WHERE col IS NULL or WHERE col IS NOT NULL evaluates the logical nullness of the column. When the column is defined as DEFAULT '' NOT NULL, the optimizer may rewrite col IS NOT NULL to the constant‑false expression NULL IS NOT NULL, causing the statement to return zero rows without scanning the table.

2. Functions such as DUMP(col) or NVL(col, 'is null') reveal the actual stored value. Using these functions shows that the column still contains NULL even though it is declared NOT NULL.

For IS NULL, Oracle may perform a full table scan and omit the col IS NOT NULL filter, returning all rows and giving the false impression that the column never contains nulls.

Root Cause – Oracle 11g Default‑Value Storage

Oracle 11g introduced a feature where the default value of a newly added NOT NULL column is stored in the data‑dictionary table sys.ecol$ instead of being written to each existing row. Oracle also allows the default of a NOT NULL column to be NULL. This combination leads to the observed anomalies where a column declared NOT NULL can appear to hold NULL values.

Version‑Specific Tests

The following script demonstrates the behavior on different Oracle releases:

create table bisal (id number);
insert into bisal values (1);
alter table bisal add name varchar2(10) default '' not null;

Oracle 10.2.0.3 : The statement fails with ORA‑01407 because Oracle tries to update existing rows with the default value, which violates the NOT NULL constraint.

Oracle 11.2.0.1 : The column is added successfully. Existing rows show NULL for the new NOT NULL column, confirming that a NOT NULL column can contain NULL values under this version.

Oracle 12.1.0.2 : Adding a NOT NULL column with a default of NULL is prohibited (ORA‑01758: table must be empty to add mandatory (NOT NULL) column). After deleting all rows, the column can be added, but inserting a NULL into it is still disallowed.

Oracle documentation for 11g confirms that the default value of a NOT NULL column is stored in the data dictionary rather than being applied to existing rows. In 12c the behavior is extended: a column with a default value may be declared NULL, but the default is still not back‑filled; the data dictionary handles the storage.

Conclusion

Oracle 12c resolves the 11g bug that allowed NULL values in columns declared NOT NULL while preserving the data‑dictionary storage of default values introduced in 11g. Understanding this subtle behavior is essential because it can produce misleading query results if the optimizer treats the column as always non‑null.