ASA SQL User's Guide
  Ensuring Data Integrity
    Using table and column constraints

Using CHECK constraints on columns

You use a CHECK condition to ensure that the values in a column satisfy some definite criterion or rule. For example, these rules or criteria may simply be required for data to be reasonable, or they may be more rigid rules that reflect organization policies and procedures.

CHECK conditions on individual column values are useful when only a restricted range of values are valid for that column.

Example 1 

• You can enforce a particular formatting requirement. For example, if a table has a column for phone numbers you may wish to ensure that users type them all in the same manner. For North American phone numbers, you could use a constraint such as:

  ALTER TABLE customer
  MODIFY phone
  CHECK ( phone LIKE '(___) ___-____' )

Example 2 

• You can ensure that the entry matches one of a limited number of values. For example, to ensure that a city column only contains one of a certain number of allowed cities (say, those cities where the organization has offices), you could use a constraint such as:

  ALTER TABLE office
  MODIFY city
  CHECK ( city IN ( 'city_1', 'city_2', 'city_3' ) )

• By default, string comparisons are case insensitive unless the database is explicitly created as a case-sensitive database.

Example 3 

• You can ensure that a date or number falls in a particular range. For example, you may require that the start_date column of an employee table must be between the date the organization was formed and the current date using the following constraint:

  ALTER TABLE employee
  MODIFY start_date
  CHECK ( start_date BETWEEN '1983/06/27'
                     AND CURRENT DATE )

• You can use several date formats. The YYYY/MM/DD format in this example has the virtue of always being recognized regardless of the current option settings.

Column CHECK tests only fail if the condition returns a value of FALSE. If the condition returns a value of UNKNOWN, the change is allowed.