Enforcing database integrity

Question

Would this ever make sense to have application enforce the database integrity instead of having foreign keys, check constraints, etc?

How much of a performance improvement one can expect for not enforcing database integrity through internal database tools?

Answer 1

Truth be told, not only will you not see much performance loss from having foreign key constraints in the database, but you will see performance enhancements. The SQL Server query optimizer is built around the concept of primary and foriegn keys as well as other types of data constraints. If these are in place and enforced the optimizer can take advantage of them to get you better performance. Here's a blog post with a simple example that shows it in action.

If you are in an edge case where you truly have more inserts than reads (and updates & deletes require reads, so they usually end up adding to the read count), then it might make sense to remove constraints from the data for performance, maybe. But since the overwhelming majority of databases are read oriented, you're sacrificing performance, not enhancing it.

And none of this mentions the fact that data integrity is better handled at the database since you only have to create it once where as if you do all the work in code, you may have to do it multiple times for multiple apps (unless you design your data access layer carefully and require every app access the db to go through that same layer).

If you're using a relational database system, I say, why not really use it. If you don't need relational data, go with Hadoop or something else.

Answer 2

There are a few times when constraints get in the way:

1. When you need to use Single Table Inheritance (STI). Imagine you sell to both individuals and organizations. You will need a single "Party" table whose row is either an individual or an org. STI means you need some nullable fields that shouldn't be null. Class Table Inheritance solves this, but this is harder for some ORMs. Ruby's ActiveRecord only supports STI, for example.

2. When you need to support Draft versions of an entity, that may not be completely valid. You could store a draft as json, but then it's harder to reuse the same identifier on the client - imagine it's been saved with id=5, edited to be not valid, and autosaved as draftid=99. In this case all of your fields would probably have to be nullable.

Answer 3

A lot of application developers think so.

When you're tempted to delegate data integrity to application code, think "Every programmer and every application that hits this database from now until the end of time has to get it perfectly right, every time."

What are the odds?

Answer 4

It's common practice to drop constraints (foreign keys, CHECK, etc) and indexes if you're doing a large enough data load, and re-enable/implement the constraints & indexes afterwards. That validation has a time cost. That's assuming you can't use database specific bulk load syntax (incl. minimizing logging).

It's impossible to say how much of a performance increase to expect - each situation is unique (data types, design, etc). The only way to truly know is to test.

Answer 5

Even if there is any performance gain, it's negligable compared to the return of referential integrity and generalized data integrity.

Long gone are the days where a database is a dumb data store. Leverage the power that RDBMS' offer.

Performance gains aren't everything, especially on such a small scale as this. But when you find out you have a supposed foreign key relationship that your application is supposed to enforce, and it turns out that is not a primary key in the referencing table then you'll care very little about performance gain (if any, I can't speak on the specifics of that).