Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL Data Definition Language : Maintaining data integrity : Types of integrity constraints : Table-level check constraints
Table-level check constraints
Your application might be required to enforce rules on multiple columns. To specify a constraint on more than one table column, define the constraint at the table level. For example, you might need to enforce a validation check on both the status and the city columns in the supplier table.
Example: Table-level check constraint
In the following 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.
CREATE TABLE supplier (
last_name CHAR (30),
supplier.status BETWEEN 1 AND 100 ),
city CHAR (20)
CHECK ( <> '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.
Example: Table-level check constraint violation
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.
INSERT INTO supplier VALUES (1001, 'John', 40, 'CHICAGO') ;