DB2 Version 10.1 for Linux, UNIX, and Windows

Check constraints on XML columns

A check constraint allows you to place certain restrictions on XML columns. The constraint is enforced whenever an attempt is made to insert or update data in the XML column; only when the criteria specified by the constraint evaluate as true is the operation performed.

When working with XML documents, an important consideration is whether or not these documents have previously been validated against XML schemas. If you need to ensure that you query, insert, update or delete only those documents that meet certain validation criteria, use the VALIDATED predicate to provide your criteria. Note that a check constraint never validates XML documents, it only tests whether or not XML documents have already been validated.¹

The VALIDATED predicate checks the validation state of the value specified by XML-expression, which must have an XML data type. If the optional according-to-clause is not specified, then the XML schema used for validation does not impact the result. Check constraints do not validate the XML documents themselves; only the current validation state of the document is tested by the constraint (IS VALIDATED or IS NOT VALIDATED). If the according-to-clause is specified, then the XML schema used to validate the value specified by XML-expression must be an XML schema identified by the according-to-clause. XML schemas need to be registered with the XML schema repository before they can be referenced in a VALIDATED predicate.

Notes:

Check constraints have a dependency on the XML schemas they reference. If the XSR object of an XML schema is dropped, any constraint that references the schema is also dropped.
XML columns support NOT NULL constraints.
XML columns support information constraints defined for XML validation.

Evaluation of check constraints

Check constraints test the validation state of documents based on the outcome of the IS VALIDATED predicate. If the condition you specified is satisfied, the constraint evaluates as true; if not satisfied, the outcome evaluates as false. If the value specified by XML-expression is null, the result of the predicate is unknown.

The result of the VALIDATED predicate is true if the value specified by XML-expression is not null AND:

an according-to-clause was not specified and the value specified by XML-expression has been validated OR
an according-to-clause was specified and the value specified by XML-expression has been validated using one of the XML schemas identified by the according-to clause.

The result of the predicate is false if the value specified by XML-expression is not null AND:

an according-to-clause was not specified and the value specified by XML-expression has not been validated OR
an according-to-clause was specified and the value specified by XML-expression has not been validated using one of the XML schemas identified by the according-to clause.

In those cases where the optional according-to-clause is specified, IS NOT VALIDATED will return true when the value specified by XML-expression has not been validated or the value specified by XML-expression has been validated but not according to any of the specified XML schemas.

Equivalence of expressions

The VALIDATED predicate

  value1 IS NOT VALIDATED optional-clause

is equivalent to the search condition

  NOT(value1 IS VALIDATED optional-clause)

Examples

Example: Select only validated XML documents. Assume that column XMLCOL is defined in table T1. Retrieve only those XML values that have been validated by any XML schema:

  SELECT XMLCOL FROM T1
    WHERE XMLCOL IS VALIDATED

Example: Enforce the rule that values cannot be inserted or updated unless they have been validated. Assume that column XMLCOL is defined in table T1 and add a check constraint to XMLCOL:

  ALTER TABLE T1 ADD CONSTRAINT CK_VALIDATED
    CHECK (XMLCOL IS VALIDATED)

Example:The constraint INFO_CONSTRAINT is an informational constraint. The rule that values cannot be inserted or updated unless they have been validated is not enforced:

CREATE TABLE xmltab (ID INT, 
     DOC XML, CONSTRAINT INFO_CONSTRAINT CHECK (DOC IS VALIDATED) NOT ENFORCED)

Informational constraints are used to improve query performance.

¹ If you need to automatically validate XML documents before they are stored in an XML column, you can use a BEFORE trigger.