Designing informational constraints

Constraints that are enforced by the database manager when records are inserted or updated can lead to high amounts of system activity, especially when loading large quantities of records that have referential integrity constraints. If an application has already verified information before inserting a record into the table, it might be more efficient to use informational constraints, rather than normal constraints.

Informational constraints tell the database manager what rules the data conforms to, but the rules are not enforced by the database manager. However, this information can be used by the Db2® optimizer and could result in better performance of SQL queries.

The following example illustrates the use of information constraints and how they work. This simple table contains information about applicants' age and gender:

    CREATE TABLE APPLICANTS
      (
       AP_NO	INT NOT NULL,
	        GENDER 	CHAR(1) NOT NULL,
			 CONSTRAINT GENDEROK
				   CHECK (GENDER IN ('M', 'F'))
				   NOT ENFORCED
				   ENABLE QUERY OPTIMIZATION,
	         AGE	 INT NOT NULL,
			 CONSTRAINT AGEOK
			  	 CHECK (AGE BETWEEN 1 AND 80)
				   NOT ENFORCED
				   ENABLE QUERY OPTIMIZATION,
     );

This example contains two options that change the behavior of the column constraints. The first option is NOT ENFORCED, which instructs the database manager not to enforce the checking of this column when data is inserted or updated. This option can be further specified to be either TRUSTED or NOT TRUSTED. If the informational constraint is specified to be TRUSTED then the database manager can trust that the data will conform to the constraint. This is the default option. If NOT TRUSTED is specified then the database manager knows that most of the data, but not all, will not conform to the constraint. In this example, the option is NOT ENFORCED TRUSTED by default since the option of trusted or not trusted was not specified.

The second option is ENABLE QUERY OPTIMIZATION which is used by the database manager when SELECT statements are run against this table. When this value is specified, the database manager will use the information in the constraint when optimizing the SQL.

If the table contains the NOT ENFORCED option, the behavior of insert statements might appear odd. The following SQL will not result in any errors when run against the APPLICANTS table:

    INSERT INTO APPLICANTS VALUES
	    (1, 'M', 54),
	    (2, 'F', 38),
	    (3, 'M', 21),
	    (4, 'F', 89),
	    (5, 'C', 10),
	    (6, 'S',100),

Applicant number five has a gender (C), for child, and applicant number six has both an unusual gender and exceeds the age limits of the AGE column. In both cases the database manager will allow the insert to occur since the constraints are NOT ENFORCED and TRUSTED. The result of a select statement against the table is shown in the following example:

    SELECT * FROM APPLICANTS
      WHERE GENDER = 'C';
    
    APPLICANT  GENDER  AGE
    ---------  ------  ---
    
    0 record(s) selected.

The database manager returned the incorrect answer to the query, even though the value 'C' is found within the table, but the constraint on this column tells the database manager that the only valid values are either 'M' or 'F'. The ENABLE QUERY OPTIMIZATION keyword also allowed the database manager to use this constraint information when optimizing the statement. If this is not the behavior that you want, then the constraint needs to be changed through the use of the ALTER TABLE statement, as shown in the following example:

    ALTER TABLE APPLICANTS
      ALTER CHECK AGEOK DISABLE QUERY OPTIMIZATION

If the query is reissued, the database manager will return the following correct results:

    SELECT * FROM APPLICANTS
      WHERE SEC = 'C';
    
    APPLICANT	GENDER  AGE
    ---------  ------  ---
            5  C       10
    
    1 record(s) selected.

Note: If the constraint attributes NOT ENFORCED NOT TRUSTED and ENABLE QUERY OPTIMIZATION were specified from the beginning for the table APPLICANTS, then the correct results shown previously would have been returned after the first SELECT statement was issued.

The best scenario for using NOT ENFORCED TRUSTED informational constraints occurs when you can guarantee that the application program is the only application inserting and updating the data. If the application already checks all of the information beforehand (such as gender and age in the previous example) then using informational constraints can result in faster performance and no duplication of effort. Another possible use of informational constraints is in the design of data warehouses. Also, if you cannot guarantee that the data in the table will always conform to the constraint you can set the constraints to be NOT ENFORCED and NOT TRUSTED. This type of constraint can be used when strict matching between the values in the foreign keys and the primary keys are not needed. This constraint can also still be used as part of a statistical view enabling the optimization of certain SQL queries.