UNIQUE keyword semantics

The same UNIQUE keyword that is used for indexes on non-XML columns is also used for indexes on XML columns, but it has a different meaning.

For relational indexes, the UNIQUE keyword in the CREATE INDEX statement enforces uniqueness across all rows in a table. For indexes over XML data, the UNIQUE keyword enforces uniqueness within a single XML column across all documents whose nodes are qualified by the XML pattern. The insertion of a single document may cause multiple values to be inserted into a unique index; these values must be unique in that document and in all other documents in the same XML column. Note also that the insertion of some documents may not result in any values being inserted into an index; uniqueness is not enforced for these documents.

Uniqueness is enforced for the data type of the index, the XML path to the node, and the value of the node after the XML value has been converted to the SQL data type that is specified for the index.

Caution should be used when specifying the UNIQUE keyword. Because converting to the specified data type for the index might result in a loss of precision or range, or different values might be hashed to the same key value, multiple values that appear to be unique in the XML document might result in duplicate key errors. Situations under which duplicate key errors can occur are:
  • When VARCHAR HASHED is specified, unique character strings may hash to the same hash code and result in a duplicate key error.
  • For an index of type DOUBLE, a loss of precision or values that are beyond the range of the DOUBLE data type can cause duplicate key errors during insertion. For example, big integer and unbounded decimal values may lose precision when they are stored as the DOUBLE data type in the index. For these kinds of data, it is better to create an index of type INTEGER, or DECIMAL, respectively.

If VARCHAR(integer) is specified, then the entire character string from the XML document is stored in the index so that incorrect duplicate key errors cannot occur. In addition, uniqueness of character strings follows XQuery semantics, where trailing blanks are significant. Therefore, values that would be duplicates in SQL but differ in trailing blanks are considered unique values in an index over XML data.

CREATE UNIQUE INDEX EMPINDEX ON company(companydocs)
    GENERATE KEY USING XMLPATTERN '/company/emp/name/last' AS SQL 
    VARCHAR(100)
For UNIQUE indexes, the XML pattern must specify a single complete path and may not contain any of the following:
  • a descendant axis
  • a descendant-or-self axis
  • a /descendant-or-self::node()/ (//)
  • any wildcards for the XML name test
  • a node() or processing instruction() for the XML kind test