Parting Shots

A column constraint restricts the number of potential values that the column may contain, thereby giving the column more specificity. The DBMS optimizer likes specificity, so constraints are hints to the optimizer. For example, many DBMSs will prefer a UNIQUE column as a driver for joining.

Constraints are also hints to the programmer, because most DBMSs don't make full use of them. The simplest example is a CHECK constraint whose logic is repeated in the WHERE clause, like this:

 CREATE TABLE Table1 (    column1 SMALLINT,    column2 CHARACTER(5),    ...    CHECK (column1 BETWEEN 15 AND 30)) SELECT * FROM Table1   WHERE column2 = 'HARRY'     AND column1 > 10 

Clearly the AND column1 > 10 clause could be eliminated in the query (because it can never be false anyway)if you can tolerate the possibility that column1 contains NULL. When applying this, remember that CHECK constraints have to be "other than false ," but WHERE clauses have to be true .

Constraint checks happen at statement end. Data type checks happen at statement start. Therefore it's theoretically slow to say (using a constraint check):

 CREATE TABLE Table1 (   column1 SMALLINT,   CHECK (column1 BETWEEN -9 AND 9)) 

as opposed to (using a data type check):

 CREATE TABLE Table1 (   column1 DECIMAL(1)) 

In practice, though, the CHECK constraint is better because it's more reliable. Some DBMSs will fail to return an error message if you insert values like 99 into a DECIMAL(1) column.