CREATE INTEGRITY
Valid in: SQL, ESQL, OpenAPI, ODBC, JDBC, .NET
The CREATE INTEGRITY statement creates an integrity constraint for the specified base table.
The CREATE INTEGRITY statement has the following format:
[EXEC SQL] CREATE INTEGRITY ON tablename [corr_name]
IS search_condition
tablename
Specifies the table for which the constraint is defined.
corr name
Specifies a
correlation name (see
Correlation Names) for the table for use in the
search condition.
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
Last modified date: 11/09/2022