Dynamism and Database Referential Integrity

[David Bolen]

kzaragoza at mediaone.net (Kris J. Zaragoza) writes:

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

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

I see transactions slightly differently as constraints since I don't
see any effective way to enforce transactions without support from the
database engine as it is the only arbiter of what all the connections
"see" at any moment in time.

Is there a key differentiator between where you put the enforcement of
such integrity rules - is it strictly performance (such as can be the
case by letting the SQL do more of the work on behalf of the
application)?

-- 
-- David
-- 
/-----------------------------------------------------------------------\
 \               David Bolen            \   E-mail: db3l at fitlinxx.com  /
  |             FitLinxx, Inc.            \  Phone: (203) 708-5192    |
 /  860 Canal Street, Stamford, CT  06902   \  Fax: (203) 316-5150     \
\-----------------------------------------------------------------------/