Programming Oracle® Triggers and Stored Procedures, Third Edition

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 ...