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.