Data Integrity Constraints

Multivalue database products do not normally impose validation rules when writing data to files. Instead, they largely rely on validation being performed when data is entered into the system. In some products, developers who wish to apply validation can do so via a pre-write trigger, rejecting the write request if the validation fails.

QM allows use of a pre-write trigger but also provides an alternative mechanism for hashed files where the validation rules for each field are defined in the dictionary and encoded into the data file in a manner that makes it impossible to skip the validation. This feature is known as Data Integrity Constraints. If both a pre-write trigger and data integrity constraints are present, the trigger is executed before the constraint validation.

The constraints are defined by an expression stored in field 12 of the A/D/S-type dictionary record for each field that is to have rules applied. Where multiple dictionary items define different views of the same field, the constraints expression must appear in only one of them. Constraints apply to the actual internal format data stored in the record, not calculated values from I-type records or A/S-type records with a correlative expression. Fields for which no constraints are defined are written with no validation.

Failure of constraint validation can be handled in several ways:

•If the full constraint validation system is enabled, the write operation is abandoned, taking the ON ERROR clause of the WRITE, if present, or the optional ELSE clause. If neither of these is present, QM throws a SYS.FILESYS.CONSTRAINTS exception or, if there is no suitable exception handler, aborts.

•Validation can operate in logging mode where details of a validation error is written to the error log but the write is allowed to continue.

•Validation can be disabled, allowing potentially invalid data to be written and subsequently reported using the VERIFY.CONSTRAINTS command described below.

These three modes can be selected separately on a file by file basis.

If a validation error occurs, the field number will be in @FNO and the corresponding field name will be in @FNAME.

Data integrity constraints are fully compatible with both record level and field level encryption. In the latter case, the constraints validation is skipped for any field that is not accessible by the user applying the update.

When using data replication, the data integrity constraints validation does not occur on the subscriber system as it would have already been done on the publisher.

Defining Constraints

A constraint definition is an expression similar to an I-type with some restrictions:

oThe SUBR() function to execute a catalogued subroutine is not allowed.

oThe TRANS(), RTRANS() and XLATE() functions or the equivalent T-conversions are not allowed.

oReferences to evaluated dictionary expressions (I-type, C-type, correlatives) are not allowed.

Three special data names are provided:

oThe data for the field being validated is available as @DATA

oThe record id is available as @ID

oThe entire record is available as @RECORD

Note that these names are private to the constraints validation expression and are not the same as use of these names in other contexts such as @RECORD in a query processor command.

When validating a field that is defined as multivalued in the dictionary, multivalued functions such as the EQS() function should be used. If the result of the expression is multivalued, all values must comply with the constraint rule.

Compound expressions are supported with intermediate results stored as @1, @2, etc exactly as in an I-type expression.

Five special functions available only in constraints expressions are provided to handle common validation criteria:

Note that all but the last of the functions listed above operate against the @DATA item without this being named in the function call.

If the field being validated is defined as multivalued in the dictionary, these functions operate on all values, returning a multivalued list of True/False results.

Examples

A simple constraints validation expression to check that the field is five digits might be written as

@DATA MATCHES '5N'

This could also be written as

MATCHING('5N')

A stock file might have both a cost price and selling price field. The validation for the selling price might be

INTEGER(100, 50000) AND @DATA > COST

to impose a range of prices from 1.00 to 500.00 (using the internal data format) and checking that the selling price is greater than the cost price.

A file might contain a multivalued list of dates that must be in January or July. This can be enforced with an expression that uses multivalued functions:

OCONVS(@DATA, "DM"); ORS(EQS(@, REUSE(1)), EQS(@, REUSE(7)))

Compiling the Constraint Definitions

The data integrity constraint definitions in the dictionary must be compiled and stored in the data file before they take effect. This is done using the COMPILE.CONSTRAINTS command:

COMPILE.CONSTRAINTS file.name

If file.name is a multi-file, an individual subfile can be updated

COMPILE.CONSTRAINTS file.name,subfile.name

or all subfiles can be updated together by giving the subfile name as an asterisk:

COMPILE.CONSTRAINTS file.name,*

The default behaviour of the COMPILE.CONSTRAINTS command is to enable full validation, failing any attempt to write faulty data. The LOGGING and DISABLE keywords to this command select the other two styles of validation described above.

Verifying Constraints

When constraints are first defined or updated for an existing file, it may be useful to verify that all records already in the file meet the constraint conditions. The VERIFY.CONSTRAINTS command does this:

VERIFY.CONSTRAINTS file.name

As with the COMPILE.CONSTRAINTS command, all subfiles of a multi-file can be processed in a single command by use of an asterisk as the subfile name.

The VERIFY.CONSTRAINTS command shows the record ids and failing field number of all records that fail validation. On completion of the command, the @SYSTEM.RETURN.CODE variable is zero for success, negative for a command error or a positive number of records that failed.

An application can use the QMBasic VALIDATE() function to verify whether a record meets the constraint rules without attempting to write it.

OK = VALIDATE(fvar, id, data)