CHECK | SQL Performance Tuning

A CHECK constraint is used to enforce a rule that a column may contain only data that falls within some specific criteria. To segue from NOT NULL constraints to CHECK constraints, let's ask the nasty questionIs it redundant to have both? At first glance, the answer appears to be "Yes." For example, suppose you have a table defined like this:

 CREATE TABLE Table1 (    column1 INTEGER NOT NULL,    ...    CHECK (column1 < 10)    ...)

Obviously a value that "must be less than ten" must automatically be NOT NULL anyway, right? Wrong. One of SQL's little-known details is that a CHECK constraint doesn't have to be true . It only has to be "other than false " ^[3] which means a NULL would be accepted by the CHECK constraint. Therefore a NOT NULL constraint is not a redundancy in this example.

^[3] Actually, the SQL Standard doesn't really say this, but most people have interpreted it that way.

Portability

InterBase is the exception. With InterBase, the CHECK constraint would reject NULL. MySQL doesn't support CHECK constraints.

In general, you use CHECK constraints to ensure that the value of a column falls in a fixed range. This means that a constraint like:

 ... CHECK (column1 < 10)

is slightly suspect. In practice, most integer columns fall within an unsigned rangefor example:

 ... CHECK (column1 BETWEEN 0 AND 9)

or should be convertible to any fixed decimalfor example:

 ... CHECK (column1 >= -99999999 AND column1 <= +99999999)

Either because of poor performance or because the syntax is not portable, CHECK constraints should not contain:

Column-to-niladic-function comparisons like:
```
 ... CHECK (date_column > CURRENT_DATE) 
```
(This is illegal in IBM, Microsoft, Oracle, and the SQL:1999 Standard.)
Column-to-subquery comparisons like:
```
 ... CHECK (column1 IN (SELECT column1 FROM Table1)) 
```
(Such CHECKs could lead to trouble with some of the transaction isolation levels that are the subject of Chapter 15, "Locks.")

Foreign keys or stored procedures work better in both cases.

CHECK constraints should be defined on the column, rather than on the column's domain or user -defined type (UDT) definition. The reason is primarily that most DBMSs do not support domains, but also because a domain constraint is not directly associated with the column in the catalog and can therefore take longer to look up.

By the way, with most DBMSs, it's possible to define constraints with conflicting conditionsthe DBMS won't stop you. For example, you would be allowed to define these constraints on the same table:

 ... CHECK (column1 BETWEEN 5 AND 10) ... CHECK (column1 < 2)

The result, of course, is that you could then INSERT only NULLs into column1 because no number satisfies both constraints simultaneously . Because the DBMS likely has no check for this situation, it's always a good idea to check all existing constraints on a table for conflicts before creating a new one.

It's technically possible that a CHECK constraint will not be violated at the time you update a row, but it will be violated by the time the SQL statement ends. This situation can arise because of CASCADEs, because of triggers, or because the constraint depends on values of other rows that are updated later in the same statement. Because of these possibilities, an SQL Standard-compliant DBMS will wait until the end of the statement before testing a constraint for violations. Thus, every row has to be accessed twiceonce to change it, and a second time later on to test for constraint violations. If a data-change statement affects hundreds of rows, there is no guarantee that the updated row will still be in cache by the time the second access occurs.

The Bottom Line: CHECK Constraints

Don't try to save time by omitting a NOT NULL constraint if you've already added a CHECK constraint. Use NOT NULL and CHECK constraints togetherunlike a search condition, constraints just need to be "other than false " in order to be satisfied.

Instead of using CHECK constraints, use FOREIGN KEY constraints or stored procedures for these types of comparisons: (a) column to niladic-function and (b) column to subquery.

Define your CHECK constraints on columns, not on domains or UDT definitions.