3.4 Check

Declaring a database column to store a person's age starts with the following:

 CREATE TABLE temp (age NUMBER); 

This command will work, but the range of the data type far surpasses the domain of a person's age. The goal is to restrict one's age to the range: 1 to 125 ”any value outside that range is rejected. A dimension on the datatype can impose a restriction on the column so that any value, outside a three-digit number, is invalid data.

 CREATE TABLE temp (age NUMBER(3)); 

A dimension scales down the range of valid values. Still, values far greater than 125 can be inserted ”any three-digit number is possible. An age of 999 is not acceptable. In general, a CHECK constraint is used to restrict the data to a real world domain. To restrict values to integer values between 1 and 125, create a check constraint on the column.

 CREATE TABLE temp (age NUMBER(3)); ALTER TABLE temp ADD CONSTRAINT ck_temp_age CHECK     ((AGE>0) AND (AGE <= 125)); 

Now, the set of values we are able to insert into AGE is the set (1, 2, 125). This matches our real-world domain, with one exception, a NULL insert.

The aforementioned CREATE and ALTER TABLE statements permit a NULL. That may be within the business rules. Maybe the database does not store an age for every person. If this is the case then the aforementioned DDL is acceptable and enforces the rule.

To restrict the aforementioned AGE column to the 1 “125 range and not permit nulls, attach a NOT NULL constraint. The DDL with the NOT NULL enforcement is now:

 CREATE TABLE temp (age NUMBER(3) NOT NULL); ALTER TABLE temp ADD CONSTRAINT ck_temp_age CHECK   ((AGE>0) AND (AGE<=125)); 

The response from a CHECK constraint violation is an ORA error with the -2290 error number.

  SQL>  insert into temp values (130);  insert into temp values (130)   *   ORA-02290: check constraint (SCOTT.CK_TEMP_AGE) violated.  

When a row is inserted or updated and there is a check constraint, Oracle evaluates the check constraint as a Boolean expression. For the aforementioned check, the row is inserted provided the expression "the AGE is within the (1,125) range" evaluates to true. The row is rejected if it is not TRUE.

The CHECK constraint does not have to be a continuous range. Suppose we want to constrain a column value to the following boundaries.

 (NOT NULL) AND (range in 0-10 OR 999 OR 9999) 

The check constraint for this would be the following.

 CREATE TABLE temp (a NUMBER); ALTER TABLE temp ADD CONSTRAINT ck_temp_a CHECK   (((a>=0) AND (a<=10)) OR (a=999) OR (a=9999)); 

Oracle does not evaluate the logic of the constraint defined in the DDL statement. The following constraint will never allow you to insert a row but you will certainly have no trouble creating it:

 ALTER TABLE temp ADD CONSTRAINT ck_temp_age CHECK   ((AGE<0) AND (AGE>=125)); 

Check constraints can be used to implement a Boolean constraint in a database column. Some databases have a Boolean type; there is no BOOLEAN table column type in Oracle ”there is a BOOLEAN datatype in PL/SQL. To simulate a Boolean column use a check constraint:

 CREATE TABLE temp(enabled NUMBER(1) NOT NULL); ALTER TABLE temp ADD CONSTRAINT ck_temp_enabled CHECK   (enabled IN (0, 1)); 

You can use a VARCHAR2 as well. The following is another approach:

 CREATE TABLE temp(enabled VARCHAR2(1) NOT NULL); ALTER TABLE temp ADD CONSTRAINT ck_temp_enabled CHECK   (enabled IN ('T', 'F', 't', 'f')); 

You can restrict a column to a discrete set of character string values. The following uses a check constraint to limit the values of a status field: 'RECEIVED,' 'APPROVED,' 'WAITING APPROVAL.'

 CREATE TABLE temp(status VARCHAR2(16) NOT NULL); ALTER TABLE temp ADD CONSTRAINT ck_temp_status CHECK  (status IN  ('RECEIVED','APPROVED','WAITING APPROVAL')); 

3.4.1 Multicolumn Constraint

A CHECK constraint can be a composite of several columns . The following table stores the dimensions of a box. We want to restrict each dimension of the box to a range within 1 and 10, plus the volume of the box must be less than 100. This means a range constraint on each column plus a constraint on the product of the dimensions.

 CREATE TABLE box  (length NUMBER(2) NOT NULL,   width  NUMBER(2) NOT NULL,   height NUMBER(2) NOT NULL); ALTER TABLE box ADD CONSTRAINT ck_box_volume CHECK   ((length*width*height<100) AND    (length >  0) AND (length <= 10) AND    (width  >  0) AND (width  <= 10) AND    (height >  0) AND (height <= 10)); 

An insert of a zero dimension or a combination of dimensions that exceed the approved volume will generate the same constraint error. Both of the following INSERTS fail.

 insert into box values (0,2,3); insert into box values (8,8,8); 

The error from each insert will be the following ORA constraint error:

  ORA-02290: check constraint (SCOTT.CK_BOX_DIMENSION) violated.  

You can declare multiple constraints with different names . This accomplishes the same goal of enforcing a single constraint. The only advantage is that one can see, more specifically , the exact nature of the error based on the constraint name . The following declares a separate constraint for each range constraint and one final constraint for the volume restriction.

 CREATE TABLE box  (length NUMBER(2) NOT NULL,   width  NUMBER(2) NOT NULL,   height NUMBER(2) NOT NULL); ALTER TABLE box ADD CONSTRAINT ck_box_length CHECK   ((length > 0) AND (length <= 10)); ALTER TABLE box ADD CONSTRAINT ck_box_width CHECK   ((width > 0) AND (width <= 10)); ALTER TABLE box ADD CONSTRAINT ck_box_height CHECK   ((height > 0) AND (height <= 10)); ALTER TABLE box ADD CONSTRAINT ck_box_dimension CHECK  ((length*width*height<100)); 

The same insert statements, used earlier, still fail, but the constraint name is more specific because each constraint enforces one part of the overall rule. Repeating the inserts:

 insert into box values (0,2,3); insert into box values (8,8,8); 

gives the following errors from SQL*Plus:

  ORA-02290: check constraint (SCOTT.CK_BOX_LENGTH) violated.   ORA-02290: check constraint (SCOTT.CK_BOX_DIMENSION) violated.  

The difference between declaring a single constraint to enforce an overall rule or separate individual constraints is a style issue.

3.4.2 Supplementing Unique Constraints

Check constraints can be used to enforce a multicolumn NOT NULL constraint that stipulates that the columns are both NULL or both NOT NULL. This is a common practice with concatenated UNIQUE constraints. A table that stores student information will have a primary key but other columns may have a unique constraint; for example, a student driver's license ”this information consists of a state abbreviation and license number. A student may not have a license; in this case both columns are null. If a student has a license, then both columns are NOT NULL and are further governed by a UNIQUE constraint.

A UNIQUE constraint enforces uniqueness among all NOT NULL values. For a concatenated UNIQUE constraint, one column can be NULL; the other column may not be NULL. We may not want this condition. The rule we want to enforce is:

(both columns are NULL) OR (both columns are NOT NULL)

To stipulate the type of constraint combine a CHECK constraint with the UNIQUE constraint.

 CREATE TABLE temp (pk NUMBER PRIMARY KEY, a NUMBER, b NUMBER); ALTER TABLE temp    ADD CONSTRAINT uk_temp_a_b UNIQUE (a, b); ALTER TABLE temp ADD CONSTRAINT ck_temp_a_b CHECK ((a IS NULL AND b IS NULL) OR        (a IS NOT NULL AND b is NOT NULL)); 

Given the aforementioned DDL, the following inserts will violate the check constraint:

 INSERT INTO temp VALUES (6, 1, NULL); INSERT INTO temp VALUES (7, NULL, 1); 

3.4.3 Students Table Example

The following DDL illustrates the motivation for a check-unique constraint combination. The STUDENTS create-table DDL below does not require license information for a student ”all other columns are mandatory, but STATE and LICENSE_NO can be NULL. The STATE and LICENSE_NO, if they exist, must be unique ”this is the UNIQUE constraint. The CHECK constraint enforcement is: there can never be a STATE value with a NULL LICENSE NO or a LICENSE NO value with a NULL STATE.

 CREATE TABLE students  (student_id      VARCHAR2(10) NOT NULL,   student_name    VARCHAR2(30) NOT NULL,   college_major   VARCHAR2(15) NOT NULL,   status          VARCHAR2(20) NOT NULL,   state           VARCHAR2(2),   license_no      VARCHAR2(30)); ALTER TABLE students    ADD CONSTRAINT pk_students PRIMARY KEY (student_id)    USING INDEX TABLESPACE student_index; ALTER TABLE students    ADD CONSTRAINT uk_students_license    UNIQUE (state, license_no)    USING INDEX TABLESPACE student_index; ALTER TABLE students ADD CONSTRAINT ck_students_st_lic CHECK ((state IS NULL AND license_no IS NULL) OR        (state IS NOT NULL AND license_no is NOT NULL)); 

3.4.4 Lookup Tables versus Check Constraints

Compared to using a lookup table, the following check constraint has disadvantages.

 ALTER TABLE temp ADD CONSTRAINT ck_temp_status CHECK  (status IN  ('RECEIVED','APPROVED','WAITING APPROVAL')); 

You cannot extend the list of values without dropping and recreating the constraint. You cannot write application code that will show the list of possible values, without duplicating the list within the application. The lookup table approach allows easy update and extension of the value set ”that's not the case with the aforementioned check constraint.

On the other hand, to restrict person's age to a range from 1 to 125 with a lookup table is not practical. For a range-numeric rule the best choice is a check constraint.

3.4.5 Cardinality

When check constraints are used to restrict a column to a limited set of values, we may have a potential candidate for a bit map index. This only applies to those cases where the constraint is used to limit the column to a small number of possible values. Check constraints, other than numeric checks, are often columns with low cardinality. For example, consider a check constraint that restricts a column to a person's gender: 'M' or 'F' with the following:

 CREATE TABLE temp(gender VARCHAR2(1) NOT NULL); ALTER TABLE temp ADD CONSTRAINT ck_temp_gender CHECK     (gender IN ('M', 'F')); 

The column is a candidate for a bit map index if we frequently query this table based on gender. Bit map indexes are useful when the cardinality of the column is low (i.e., has only a few possible values). The following creates a bit map index on column GENDER.

 CREATE BITMAP INDEX b_temp_gender ON TEMP   (gender) TABLESPACE student_index; 

3.4.6 Designing for Check Constraints

The pursuit of what columns need check constraints is sometimes overlooked. When developing new applications or migrating legacy systems to an Oracle database, the domain of an attribute often remains the same as the range of the data type. With this approach we would have permitted, in our earlier example, the insertion of someone's age to be 999 years . One reason for few check constraints in a database is simply the fact that no one knows the data well enough to state emphatically that there is some specific range of values. This is understandable.

Given this, you have two choices. Skip check constraints, or make some assumptions and the worst that can happen is that a record fails on a check constraint. In the latter case, you can always drop the constraint and recreate with the new rule, then rerun the application for which the insert failed.

Earlier we created a check constraint named CK_BOX_LENGTH. This constraint can be redefined with the following.

  SQL>  ALTER TABLE box DROP CONSTRAINT ck_box_length;  Table altered.   SQL>  ALTER TABLE box ADD CONSTRAINT ck_box_length CHECK  2  ((length > 0) AND (length <= 12));  Table altered.  

Another reason for few check constraints appears to be the powerful enforcement capability in the client application. JavaScript in a browser is best for verifying that a field is not blank or not zero before sending the string over the network, only to have it rejected by a constraint error. It makes a lot of sense to enforce these types of rules in the client because the end user does not have to wait to realize they made an error on data entry. However, as illustrated in Figure 3-3, data goes into the database from multiple sources ”not just end users and regardless of client-side rule enforcement, the rules of data integrity should still be in the database. Constraint checks will preserve data integrity for SQL*Plus operations, PL/SQL programs that may run nightly loads, and SQL*Loader runs that bring in data from other systems.