The UNIQUE keyword in an XML index definition

The UNIQUE keyword in XML index definitions has a slightly different meaning than it does for relational index definitions.

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 across all documents in an XML column.

For an XML index, Db2 enforces uniqueness for:
  • The data type of the index
  • The XML path to a node
  • The value of the node after the XML value has been cast to the SQL data type that is specified for the index

Because rounding can occur during conversion of an index key value to the specified data type for the index, multiple values that appear to be unique in the XML document might result in duplicate key errors.