Category list

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 term sometimes in data modeling tools when you are asked what types of constraints you want to apply to a column.

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 hand-coding a lot of DDL, you may choose not to name each constraint ”that can be a time-consuming task. Some tools, like Oracle Designer, automatically generate constraint names for you. This is a nice feature and should be used. The naming of constraints has been emphasized , rightfully so, within this chapter. However, naming NOT NULL constraints is just not as common as naming other constraints. In summary, it doesn't hurt to name them. The following illustrates some options.

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:

The Oracle constraint violation error message includes the name of the constraint. This is very helpful and allows us to quickly isolate the problem without having to look in the data dictionary or investigate the code that caused the error.

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 columns . A PL/SQL code block is used to populate the table, but with one variable reset to NULL. The Oracle error message generated specifically identifies the column constraint violated. First the table:

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 served little purpose. The error says we tried to insert a NULL into the column N2. We only have to look into the PL/SQL code and trace how that variable was set to NULL.

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.

Flylib.com

Category list

Similar pages

3.5 NOT NULL Constraints

3.5 NOT NULL Constraints

3.6 Default Values

Similar products