3.5 NOT NULL Constraints
The NOT NULL constraint is often referred to as a "mandatory" constraint (i.e., we say the AGE column is a mandatory value). You see this
The most common syntax for a not null constraint is to append NOT NULL within the column definition of the table. You can name a not null constraint. If you are
CREATE TABLE temp(id NUMBER(1) NOT NULL); CREATE TABLE temp(id NUMBER(1) CONSTRAINT nn_temp_id NOT NULL);
There is one significant reason why we name PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK constraints:
Oracle does not reference the constraint name in a NOT NULL constraint violation. It doesn't have to because the column name is included in the message. The following illustrates a table with three NOT NULL columns, but imagine this scenario with 20
CREATE TABLE temp (N1 NUMBER constraint nn_1 NOT NULL, N2 NUMBER constraint nn_2 NOT NULL, N3 NUMBER constraint nn_3 NOT NULL);
This is a short block, but it could just as well be several hundred lines of PL/SQL. The constraint error message identifies the column, saving us from isolating that part of the problem.
DECLARE v1 NUMBER := 1; v2 NUMBER := 2; v3 NUMBER := 3; BEGIN v2 := null; INSERT INTO temp VALUES (v1, v2, v3); END;
The result of running the aforementioned PL/SQL block is the following Oracle error ”notice the column name, N2.
ORA-01400: cannot insert NULL into ("SCOTT"."TEMP"."N2")
So, naming the constraint
3.6 Default Values
Consider the following table with its two
CREATE TABLE TEMP (id NUMBER, value NUMBER);
The first two INSERT statements are identical. This third
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
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
PL/SQL expressions that include NULL values can be tricky. Refer to Chapter 11, Section 11.4 for additional discussion on this topic.