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.

EXAMPLE

The 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:

EXEC SQL 
     CREATE TABLE supplier ( 
          supp_no  INTEGER NOT NULL, 
          last_name  CHAR (30), 
          status SMALLINT, 
          city  CHAR (20) CHECK ( 
          supplier.city IN ('NEWYORK', 'BOSTON', 'DALLAS', 'MANCHESTER')) 
     ) ; 

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.

EXAMPLE

In 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:

EXEC SQL 
CREATE TABLE supplier ( 
     supp_no  INTEGER NOT NULL, 
     last_name  CHAR (30), 
     status SMALLINT, 
     city  CHAR (20) CHECK ( 
     supplier.city <> 'BadApple') 
     ) ; 

For example, the following INSERT statement results in an error, and the corresponding row is not inserted into the table:

/* 
** This INSERT statement FAILS with a constraint violation. 
*/ 
EXEC SQL 
     INSERT INTO supplier VALUES (1001, 'Worm', 20, 'BadApple') ; 

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.

EXAMPLE

In 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:

EXEC SQL 
     CREATE TABLE supplier ( 
     supp_no    INTEGER NOT NULL, 
     last_name       CHAR (30), 
     status     SMALLINT CHECK ( 
          supplier.status BETWEEN 1 AND 100 ), 
     city       CHAR (20) 
     CHECK ( 
          supplier.city IN ('NEWYORK', 'BOSTON', 'CHICAGO', 'MANCHESTER' ) 
            ), 
     CHECK (supplier.city <> 'CHICAGO' OR supplier.status = 20) 
            ) 
     ) ; 

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:

/* 
** This INSERT violates a CHECK CONSTRAINT. 
*/ EXEC SQL 
     INSERT INTO supplier VALUES (1001, 'John', 40, 'CHICAGO') ; 


Copyright © 2004 Progress Software Corporation
www.progress.com
Voice: (781) 280-4000
Fax: (781) 280-4095