Editorials

Declarative Referential Integrity is In

by Ben Taylor

There is a lot of activity on the question regarding the application of Declarative Referential Integrity, ie. Should you always use it? I was surprised to see some reasonable ideas of how/when you should not use DRI.

There were some common points in all of the online comments from our readers.

  1. DRI should be the norm, not the exception.
  2. DRI is essential for OLTP systems (Online Transaction Processing). This is primarily due to the fact that most databases have more than one access point, and centralized DRI is essential for the integrity of the database.
  3. DRI may be optional in some cases. Some felt that if you are copying historical data for reporting or archival purposes where the data is basically static, then DRI may potentially be dropped. This allows the manipulation of the data to be performed without the constraints of DRI.
  4. Even if DRI is not enforced, it is a good practice to maintain the same indexes you would maintain with DRI turned on, from a performance perspective.

Thanks everyone for playing along on my experiment in temporary insanity. If you didn’t get a chance to add your opinion to the conversation feel free to make your own comments in the previous editorial or in the comment section below. Otherwise you can drop an Email on this or any other topic to btaylor@sswug.org.

Cheers,

Ben

Tags:
Author: Ben TaylorBen is a Relational Database veteran starting in the early eighties on DEC Vax database systems such as Bloom and Harvest. He has specialized in SQL engines for more than two decades, with a primary focus on Microsoft SQL Server. Ben has worked with small and large databases for both OLAP and OLTP working on systems for many industries such as Medical (Medical Claims and Eligibility Warehouse, HMO Provider Management, Enrollment), Financial (credit card processing, mortgage, stock portfolio management), Manufacturing (Assembly Line and Product Assemblies), Sales, Auto Auctions and more.