ICHECK function

Syntax

ICHECK ( dynamic.array [ , file.variable ] , key [ , column# ] )

Description

Use the ICHECK function to check if data you intend to write to an SQL table violates any SQL integrity constraints. ICHECK verifies that specified data and primary keys satisfy the defined SQL integrity constraints for an SQL table.

dynamic.array is an expression that evaluates to the data you want to check against any integrity constraints.

file.variable specifies an open file. If file.variable is not specified, the default file variable is assumed (for more information on default files, see the OPEN statement).

key is an expression that evaluates to the primary key you want to check against any integrity constraints.

column# is an expression that evaluates to the number of the column in the table whose data is to be checked. If you do not specify column#, all columns in the file are checked. Column 0 specifies the primary key (record ID).

If dynamic.array, file.variable, key, or column# evaluates to the null value, the ICHECK function fails and the program terminates with a run-time error message.

You might use the ICHECK function to limit the amount of integrity checking that is done and thus improve performance. If you do this, however, you are assuming responsibility for data integrity. For example, you might want to use ICHECK with a program that changes only a few columns in a file. To do this, turn off the OPENCHK configurable parameter, open the file with the OPEN statement rather than the OPENCHECK statement, and use the ICHECK function before you write the updated record to verify, for each column you are updating, that you are not violating the table's integrity checks.

If the ON UPDATE clause of a referential constraint specifies an action, ICHECK always validates data being written to the referenced table; it does not check the referencing table. Therefore, ICHECK can succeed, but when the actual write is done, it can have a constraint failure while attempting to update the referencing table. If the referential constraint does not have an ON UPDATE clause, or if these clauses specify NO ACTION, the referencing table is checked to ensure that no row in it contains the old value of the referenced column.

ICHECK does not check triggers when it checks other SQL integrity constraints. Therefore, a write that fires a trigger can fail even if the ICHECK succeeds.

ICHECK returns a dynamic array of three elements separated by field marks:

error.codeFcolumn#Fconstraint

Table 1. Syntax of Returned ICHECK Array
Argument	Explanation	Note
error.code	A code that indicates the type of failure. Error codes can be any of the following:
	0	No failure
	1	SINGLEVALUED failure
	2	NOT NULL failure
	3	NOT EMPTY failure
	4	ROWUNIQUE failure (including single-column association KEY)
	5	UNIQUE (column constraint) failure
	6	UNIQUE (table constraint) failure
	7	Association KEY ROWUNIQUE failure when association has multiple KEY fields.
	8	CHECK constraint failure
	9	Primary key has too many parts
	10	Referential constraint failure
	11	Referential constraint failure that occurs when a numeric column references a nonnumeric column in the referenced table.
column#	The number of the column where the failure occurred. If any part of a primary key fails, 0 is returned. If the violation involves more than one column, -1 is returned.
constraint	This element is returned only when error.code is 7 or 8. For code 7, the association name is returned. For code 8, the name of the CHECK constraint is returned if it has a name; otherwise, the CHECK constraint itself is returned.

If the record violates more than one integrity constraint, ICHECK returns a dynamic array only for the first constraint that causes a failure.

The ICHECK function is advisory only. That is, if two programs try to write the same data to the same column defined as UNIQUE (see error 5), an ICHECK in the first program might pass. If the second program writes data to the file before the first program writes the data that passed ICHECK, the first program's write fails even though the ICHECK did not fail.