3.6 Default Values

Consider the following table with its two columns :

 CREATE TABLE TEMP (id NUMBER, value NUMBER); 

The first two INSERT statements are identical. This third inserts a NULL for the column VALUE.

 INSERT INTO temp             VALUES (1, 100); INSERT INTO temp (id, value) VALUES (1, 100); INSERT INTO temp (id)        VALUES (2); 

Is this what you want? Do you want NULL? Maybe a zero would be better. The following replaces the DEFAULT NULL with a zero.

 CREATE TABLE TEMP (id NUMBER,                    value VARCHAR2(10) DEFAULT 0); 

The default for VALUE is no longer NULL. This inserts a (2, 0)

 INSERT INTO temp (id) VALUES (2); 

If a zero can be interpreted as no data, then use a DEFAULT as was done earlier. There are situations where a zero equates to no data. One example is a checking account. If there is a zero balance, there is no money. Other situations require a NULL. Consider an environmental biologist measuring microbes in a polluted river. On some days, the microbe count may be zero. On other days the river may be frozen. On these days an insert should be a NULL. A zero value, on days when the river is frozen, could skew any analysis of the data. For the environmentalist, there is a difference between a zero and a NULL. A NULL means no sample was taken. For the checking account, a zero is equivalent to the absence of money.

PL/SQL expressions that include NULL values can be tricky. Refer to Chapter 11, Section 11.4 for additional discussion on this topic.