Dynamism and Database Referential Integrity

[Stefan Schwarzer]

Hello David

David Bolen wrote:
> > When you have multiple applications accessing one database, however,
> > you don't want a bug in one app causing all the others to fail because
> > of bad data.  Here is where the various features of modern relational
> > databases really start to shine.  Referential integrity along with
> > other types of constraints help keep data consistent.  Transactional
> > controls likewise ensure that applications only ever see properly
> > saved, consistent data.
> 
> As not primarily a database person, whenever this comes up I always
> want to put the constraint logic (e.g., validation of data, handling
> erroneous data, and so on) off up in the application and not down in
> the database.  Having shared routines used by all users of the data
> ensures that all applications have the same protection.

Validation of the data to insert or update can rather easily be done by
application logic. I also think, it _should_ be done by the application
because the application is more able to provide the reason for a failure
(to select/update/insert/delete) in more abstract terms for the user.
Guessing from the database error what went wrong on the user side may
be difficult.

However, constraints which _relate_ tables (or rows or columns in a
single table) instead of belonging to individual cells are another
issue. There must be some centralized control so that one application
does not invalidate the transaction of another. This centralized control
could be in the application (and only if its really a single application,
not several applications which connect "independently" to a database
over a network) but it's probably much faster if it's handled by the
database engine, anyway.

> Of course, this is really just moving the behavior up or down the
> spectrum between database engine and application (and it's probably
> the same impulse I have to write simpler SQL and more application
> logic).

My preferences are the same as yours, but nonetheless I think it's better
to put constraint definitions in the database. :-)

Stefan