Create Integrity

Valid in: SQL, ESQL

The Create Integrity statement creates an integrity constraint for the specified base table.

Syntax

The Create Integrity statement has the following format:

[EXEC SQL] CREATE INTEGRITY ON table_name [corr_name]
              IS search_condition

• table_name

  Specifies the table for which the constraint is defined.

• corr name

  Can be specified for the table for use in the search condition. For a definition of correlation names and discussion of their use, see the chapter "Introducing SQL."

• search condition

  Defines the actual constraint. For example, if you want to create a constraint on the employee table so that no employee can have a salary of greater than $75,000, issue the following statement:

  create integrity on employee is salary <= 75000;

The search condition must reference only the table on which the integrity constraint is defined, and cannot contain a subselect or any aggregate (set) functions.

At the time the create integrity statement is executed, the search condition must be true for every row in the table, or the DBMS Server issues an error and aborts the statement. If the search condition is defined on a column that contains nulls, the statement fails unless the is null predicate is specified in the statement.

After the constraint is defined, all updates to the table must satisfy the specified search condition. Integrity constraints that are violated are not specifically reported: updates and inserts that violate any integrity constraints are simply not performed.

Locking

The Create Integrity statement takes an exclusive lock on the specified table.

Performance

The time required to execute the Create Integrity statement varies with the size of the table, because the DBMS Server must check the specified base table to ensure that each row initially conforms to the new integrity constraint.

Embedded Usage

In an embedded Create Integrity statement, variables can be used to see constant values in the search condition.

Permissions

You must own the table.

Related Statements

Drop Integrity

Examples: Create Integrity

The following are Create Integrity statement examples:

1. Make sure that the salaries of all employees are no less than 6000.

   create integrity on employee is salary >= 6000;

2. In an embedded application, define an integrity using a host language variable.

   exec sql create integrity on employee
   is sal < :sal_limit;