What advantages do constraints provide to a database?

Question

I realize this question may seem a little on the "green" side, but after the number of "enterprise" or "commercial" databases I've encountered I've begun to ask this question. What advantages to constraints provide to a database? I'm asking more about Foreign Key constraints rather than Unique constraints. Do they offer performance gains, or just data integrity?

I've been rather surprised at the number of relational databases without foreign keys or even without specified primary keys (just constraints on fields being not null or a unique constraint on the field).

Thoughts?

Answer 1

"just" data integrity? You say that like it's a minor thing. In all applications, it's critical. So yes, it provides that, and it's a huge benefit.

Answer 2

Data integrity is what they offer. If anything they have a performance cost (a very minor one at least).

Answer 3

They provide both performance and data integrity, and the latter is paramount to any serious system. I cringe every time I see a database without any foreign keys and where all integrity is done through triggers (if at all). And I saw quite a bit of those out there.

Answer 4

Integrity constraints are especially important when you integrate several applications using a shared database.

You may be able to properly manage data integrity in a single application's code (and even if you don't, at least the broken data affects only that application), but with multiple apps it gets hairy (and at the least redundant).

Answer 5

In relational theory, a database that allows inconsistent data isn't really a relational database. Foreign keys are necessary for data integrity and consistency to keep the database "relational"; i.e. the logical model of the database is always correct.

In practical terms, it's usually easier to define a foreign key and let the DB engine handle making sure the relation is valid. The other options are:

• nothing - guaranteed data corruption at some point
• DB triggers - which will usually be slower and less performant
• application code - which will eventually cause problems when either you forget to call the right code or another application accesses the database.

Answer 6

Data is an asset. Lots of textbooks state that.

But it is actually wrong. It should rather say "correct data is an asset, incorrect data is a liability".

And database constraints give you the best possible guarantee that data is correct.

Answer 7

Simpler Application Code

One nice thing they provide is that your application code has to do a lot less error checking and validation. Contrast these two bits of code and multiply by thousands of operations and you can see there's a big win.

get department number for employee  # it's good coz of constraints
do something with department number

vs.

get department number for employee
if department number is empty
    ...
else if department number not in list of good department numbers
    ....
else
    do something with department number

Of course, people that ignore constraints probably don't put a lot of effort into code validation anyway... :-/

Oh, and if the data constraints change, it's a database configuration issue and not a code change issue.

Answer 8

The following, assuming you get the constraint right in the first place:-

• Your data will be valid with respect to the constraint
• The database knows your data will be valid with respect to the constraint and can use this when querying or updating the database (e.g. removing an unnecessary join for a query on a view)
• The constraint is documented for future users of the database
• A violation of the constraint will be caught as soon as possible; not in some later unrelated process that fails

Answer 9

In some DBMSs (e.g. Oracle) constraints can actually improve the performance of some queries, since the optimiser can use the constraints to gain knowledge about the structure of the data. For some examples, see this Oracle Magazine article.

Answer 10

I would say all required constraints must be in the database. Foreign key constraints prevent unusable data. They aren't a nice to have - they are a requirement unless you want a useless database. Foreign keys may hurt performance of deletes and updates but that is OK. Is it better to take a little longer to do a delete (or to tell the application not to delete this person because he has orders in the system) or to delete the user but not his data? Lack of foreign keys may cause unexpected and often serious problems in querying the data. For instance the cost reports may depend on all the tables having related data and so may fail to show important data because one or more tables have nothing to join to.

Unique constraints are also a requirement of any decent databse. If a field or group of fields must be unique, to fail to define this at the database leve is tocreate data problems that are extremely hard to fix.

You don't mention other constraints but you should. Any business rule that must always be applied to all data in the table should always be applied in the database through a datatype (such as a datatime datatype which willnot accept '02\31\2009' as a valid date), a constraint (say one that does not allow the field to have a value greater than 100) or through a trigger is the logic is so complex it cannot be handled by an ordinary constraint. (Triggers are tricky to write if you don't know what you are doing, so if you have logic this complex, you hopefully have adatabase professional on your team.) The order is important to. Datatypes are the first choice, followed by constraints, followed by triggers as a last choice.

Answer 11

"Oh, and if the data constraints change, it's a database configuration issue and not a code change issue."

Unless it's a constraint that disappears from the design. In that case, there definitely IS a code impact, because some code might have been written that depends on that removed constraint being there.

That must always be taken in consideration when "laxing" or "removing" any declared constraint.

Other than that, you are of course completely right.