Database Level Constraints
The question of database enforced constraints vs
programmer applied constraints is asked occasionally by people who
have not done much database development work.
My advice is to choose database level constraints where possible.
Integrity constraints (foreign keys), check constraints (attribute domains)
take work out of the programmers head space and stops data from
getting scrambled.
Any programmer who thinks about it will come to the same conclusion.
For example, great wads of code written to check that an orderline is
attached to an order becomes worthless if one error pops up anywhere
or any time in any application that can change the data.
It doesn't matter how good one programmer is if another idiot gets
in there and scrambles the data. Maintenance on programs goes for
decades, many programmers of varying skills may change the code and
I never met a programmer that didn't think everyone else is an idiot.
One of the dirty secrets of ERP applications such as Baan, PeopleSoft
and SAP is that patches and updates end up costing
companies huge overhead to straighten out the "programmer enforced"
constraints.
A significant part of the processing that
happens in ERP batch jobs seems to be 'fixing' the problems
brought about by bad design, old code and non-RDBMS based
architecture which fails to use database level constraints.
The only place to use programming constraints is where shared data
is treated differently in separate applications. In that case shared
constraints are still enforced by the database but application specific
constraints have to be handled per application.
For testimonials just ask people who "clean" data for
loading into a data warehouse which type of database they would
rather load from....