Running a database without constraints

Question

We're looking at utilizing a new proprietary high performance database engine for OLAP and OLTP scenarios. One important aspect of it right now is that it does not support foreign key or other constraints. What is your opinion on whether this limitation should be considered a non-starter for OLTP applications?

Would it be crazy to think about implementing our own referential integrity checks for any inserts, updates, and deletes?

Answer 1

Rolling your own referential integrity checks has the following disadvantages:

1. Speed - Your own checks will never be as fast as database internal checks.
2. Completeness- There is always the possibility when you roll your own that you will miss something.

The speed issue is acceptable if the high performance of the database engine can make up for the loss, but the potential for a loss of data integrity using your own checks would still be a concern.

Answer 2

What is the cost of data quality issues down the line from this system? Do those costs outweigh the benefits of using this system over some other system that actually enforces referential integrity?

Unfortunately, there are plenty of applications out there (particularly those of the "database agnostic" variety) that implement their own constraints rather than relying on the database to enforce relational integrity. They have wildly divergent levels of success in ensuring that the data they produce is reasonable-- some do it quite well, others appear to create a mishmash of crud and call it data. But all of them will create at least some invalid data.

Given that, the question becomes how much the invalid data will cost you down the line to disentangle. If you're trying to, say, track the sale of securities, any trade that doesn't tie back to a valid client is going to create a problem that has to be tracked down. If you're trying to track hours on internal project, on the other hand, it's probably merely annoying if some hours worked by individuals don't tie back to a valid project. If you're going to spend thousands of man hours cleaning up the data you've generated, a system that uses constraints is going to be vastly more cost effective. If close enough is good enough, implementing your own constraints may well be sufficient.

Answer 3

I have less concern about adding constraints to an OLAP as the data feeds usually have the constraints applied. The corresponding indexes may be required to speed access. Good OLAP schemas tend not to be suitable for OLTP.

For an OLTP database, it may be faster to have the integrity constraints built into the database. If you need to do several round trips to the database to read the data required to verify the constraints you may loose the gains you get from the high performance database.

That said, I rarely see applications violating integrity constraints. You should consider building appropriate audit scripts and factor their costs into the comparison between the approaches. Data cleanup can be costly.

It may be more appropriate to reserve the high performance database for OLTP.

Answer 4

Well, MySQL with MyISAM has no FK constraints and is still used on a very broad basis, so if you take good care of the data and proper exception handling from the app, you should be fine.

Of course, things like constraints make a very nice schema and a gurantee that your data is consistent and entries point to real rows. DELETE/UPDATE constraints are also very nice addition but these things can be done with triggers (if supported).

To sum up, FK and their constraints should be part of a regular database but if these have a real high impact on performance, I think (!) you can leave them aside IF the application is able to insert and retrieve the data properly without corrputing the data.

Answer 5

When using modern ORM frameworks within your Business Logic Layer, many people ignore database level integrity checks even if the database supports them. The main thing that I would look at within your design is the API layer level for data insertion/modification. If you have full control of the data at some point above the database layer, checks within the DB might be redundant.

Answer 6

OLAP and data-warehousing require the de-normalization of tables. Hence, there may be a reason why your OLAP application does not have explicit constraints defined.