RE: Check constraints was: Imbedded SQL vs Sequence object

From: rob@xxxxxxxxx

Then, Joe, why should one have external fields? After all, if one is
going to check if an area of the record is numeric or alphanumeric in the
edit program why do it in the database? (Let alone the fact the DDS is a
sick joke and allows you to store characters in numeric fields versus
DDL.)

(sigh) So much for a civil discussion.

This has nothing to do with DDL vs. DDS; I personally prefer DDL whenever
possible. It has a couple of limitations, but that's not the point. The
point was very specific: does it make sense to split business rules between
HLL and database?

I know that you came from SSA

Yup. Millions of users worldwide. $500 million company. I guess we did
something right. By the way, that was 20 years ago -- how much of your code
from 20 years ago is still in production without modification?

, and not only did they not use primary keys
on their physical's, but they didn't even specify UNIQUE on their logical
files and, unless you restricted everything to their edits, it was quite
easy to have duplicate keys in the item master.

If your programs don't check for duplicates, you get duplicates. If you do
check for duplicates, you don't need the constraint. Unless you're actually
using constraints as edit checks, then all a uniqueness constraint does is
catch stupid programmer mistakes.

If you do use constraints as your primary edit checks, that's a completely
different philosophy.

Well, conversion
programs, etc, do not fit nicely into their edits.

That's why we moved towards server programs that you call to modify the
database. In fact, that was one of the primary reasons that I fought the
conversion to SQL; by simply converting all the crappy duplicated edits in
crappy duplicated SQL, it delayed the move to servers and basically killed
the performance of the package (and eventually the company itself).

And, as much as some people push externalizing database access so that all
access to the database is from getter and setter routines it rarely is
ever used.

But it's the correct way to do things, or at least a correct way. The fact
that lots of people write bad code is no reason not to recommend a good way.

Logical cut off point? My opinion that would be whatever couldn't be done
within the database. And, I might even suggest a before trigger as a
final dam to catch those that couldn't be done with check constraints or
referential constraints.

I really have a hard time with edits in a before trigger, in a maintenance
program, and in a constraint. What if the before trigger thinks the
maintenance program will handle it, and the maintenance program thinks the
constraint will handle it?

Me, I believe in server programs so that my edits are in one place and one
place only. Lock those puppies down and make sure they're the only programs
that can access the database. Security, auditing, platform independence,
modularization, SOA enablement, everything in one happy place.

Anyway, you have a vested interest in your opinion. I'd like to see if
everyone shares it.

Joe