Referential Integrity and XML Data
November 18, 2008
A few people have recently asked about the ability to ensure referential integrity in XML data. In this case, they are referring to the referential integrity feature that is common to relational databases. I thought I’d take a few moments to share one possible answer with you.
Referential integrity is a relational database feature that ensures consistency is maintained between items that reference one another. For instance, if you have a database table with order information that refers to a database table with product information, referential integrity ensures that each order refers to a valid product. In technical terms, referential integrity ensures that each product ID specified in the order table (where the product ID is a foreign key in the order table), exists in the product table.
Over the years, DBAs have found referential integrity to be a valuable feature in their relational databases. And now, they are asking how to ensure referential integrity with XML data.
The XML standard does not include mechanisms for ensuring referential integrity amongst XML elements. You can use schemas to place constraints on the XML data, but these constraints are not really enforced by the database (except insofar as the database is used to validate via a schema). And the ability to place constraints on XML data doesn’t quite add up to the ability to the ability to ensure the same level of consistency checking offered by referential integrity in a relational database.
However, there is a possible answer. This is one of the cases where using a hybrid relational/XML database proves to be very useful. You extract values from certain XML elements and store those values in relational columns in the same table, and then place constraints on those relational columns to ensure referential integrity. There is a cost associated with this approach, due to increased storage and additional programming logic. However, for many, this increased cost is justified by the ability to ensure referential integrity.