Progress
Embedded SQL-92
Guide and Reference
Check Constraints
The values you enter for a row must be valid so that the data in the database is consistent. For example, the city names you enter into the supplier table must correspond to one of the cities where the suppliers are located. The database checks to ensure that each value corresponds to one of the valid city names. You achieve these validations by specifying check constraints during the definition of the table schema. Use check constraints when you want to restrict a column to a set of valid values.
EXAMPLEThe following code fragment shows how to specify a check constraint on the supplier table. In this example, the city column is defined with a check constraint to verify that values for city are in the set of NEWYORK, BOSTON, DALLAS, or MANCHESTER. This CREATE statement does not use the CONSTRAINT keyword in the table definition. The system assigns a constraint name:
A check constraint on a table specifies a condition on the column values of a row in that table. Whenever you issue an INSERT or UPDATE operation against a table containing check constraints, the database validates the column values. The INSERT or UPDATE operation is completed only after successful validation.
You can specify a check constraint either at the column level or at the table level. The following sections discuss column level and table level check constraints.
Column Level Check Constraint
In an application, you might decide to check a particular column for valid data whenever you attempt to INSERT or UPDATE values for that column. For example, you design your database to disallow suppliers from the city of BadApple. Use a column level check constraint for this type of validation.
EXAMPLEIn the next example there is a column level check constraint on the city column of the supplier table; this check constraint affects the city column only. When you issue an INSERT or UPDATE operation against the supplier table involving the city column, the SQL engine validates the column value, ensuring that the column does not contain the value BadApple. If the INSERT or UPDATE statement violates the check condition, the database returns a constraint violation error:
For example, the following INSERT statement results in an error, and the corresponding row is not inserted into the table:
Table Level Check Constraint
Your application might be required to enforce rules on multiple columns. To specify a constraint on more than one column of a table you define the constraint at the table level. For example, you might want to enforce a validation check on both the status and the city columns in the supplier table.
EXAMPLEIn this example, the table level check constraint verifies that when the city is CHICAGO, the status must be 20, otherwise the operation returns a table level check constraint violation:
Since the check constraint specification involves more than one column, you must specify it at the table level. If an INSERT or UPDATE statement violates the check condition the database returns an error. The following example shows an INSERT statement for the supplier table created in the previous example. This INSERT operation results in a check constraint violation:
Copyright © 2004 Progress Software Corporation www.progress.com Voice: (781) 280-4000 Fax: (781) 280-4095 |