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?


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.

| improve this answer | |
  • 2
    That's pretty much along the lines of what i thought myself and expected. I knew that DBA at my previous job was wrong about it, just wanted to get an independent opinion on it. Thanks! – Renats Stozkovs Feb 14 '12 at 15:02

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?

| improve this answer | |
  • 5
    +1. That basically is it. You replace a well tested and central system with a requriement tons of programmers have to adhere to. EVERY time. Will not happen -so you get databases with bad data over time. – TomTom Feb 13 '12 at 12:06

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).

| improve this answer | |
  • -1. Long gone are the days people put aplication logic into the database, the hardest and msot expensive to scale part of the whole stack - for me databases are a dump store with logic run by applications. THAT SAID: Referential integrity is about database level integrity and very useful. – TomTom Feb 13 '12 at 12:05
  • 5
    @TomTom Rewriting data integrity logic in your application is redoing work that's already been done in RDBMSes. Keep data logic in the database. – Thomas Stringer Feb 13 '12 at 13:27
  • @TomTom - "Theoretical invalid data shuold never hit the database, but integrity is a last line of defense." Agreed. That fancy AJAX form will save your end-users a lot of headache by validating their input upfront. Likewise, those database constraints will save your business and your engineers just as much in time, money, and energy lost cleaning up after bad code. – Nick Chammas Apr 16 '12 at 22:28

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.

| improve this answer | |
  • 1
    +1. Note that this is a special case, though - in general data laods do notdo any processing and assume the data to be correct and will blow anyway on the recreate index step. THis is motly a data warehosue level technique. – TomTom Feb 13 '12 at 12:07

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.

| improve this answer | |

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.