Try OpenEdge Now
skip to main content
SQL Development
OpenEdge SQL Data Definition Language : Maintaining data integrity : Types of integrity constraints : Column-level check constraints
Column-level check constraints
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 a place called Toxic Island. Use a column-level check constraint for this type of validation.
Example: Column-level check constraint
In the following 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 'Toxic Island'. If the INSERT or UPDATE statement violates the check condition, the database returns a constraint violation error.
CREATE TABLE supplier (
last_name CHAR (30),
status SMALLINT,
city CHAR (20) CHECK ( <> 'Toxic Island')
) ;
Example: INSERT failure due to check constraint
In the following examples, the INSERT statement results in an error, and the corresponding row is not inserted into the table.
INSERT INTO supplier VALUES (1001, 'Worm', 20, 'Toxic Island') ;
Example: Constraint violation error message
=== SQL Exception 1 ===
[JDBC Progress Driver]:Constraint violation (7597)