RE: Check constraints was: Imbedded SQL vs Sequence object

From: Walden H. Leverich

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?

I view it the other way. It's not that the DB (constraint) should handle
the edit, it's that it's a last-line-of-defense against any program that
doesn't handle the edit.

Inserting data into a table without checking the constraints much
earlier in the program stack is just silly. Sure, you can handle the
error, decode the constraint name, and somehow notify the user in a
friendly manner from 7 program calls deeper in the stack, or you can
just check for the error in the UI in the first place and give the user
a friendly message.

The point of a constraint is to tell the db how to protect itself from
_any_ unchecked updates. Say, DFU, DBU, ODBC, whatever.

That's my sense of it, too, which is why having lots of constraints seems
like overkill, if not outright dangerous. Think about it: If you are
allowing outside access that could conceivably send data that doesn't pass
the most basic edits (such as a valid value in a field), then you are
allowing access that could send much more subtle and damaging errors
(putting the WRONG customer, not an invalid one). Constraints for edits
checks are like painting over cracks in the wall -- they don't fix the
foundation, where the problem lies.

I think Rob leans towards the before trigger, which is a possibility except
that again it doesn't really address logical errors as opposed to physical
field errors. It's closer, but it's also somewhat difficult to address
multiple-record issues (like circular references).

It seems to me that constraints, with the possible exception of actual
entity relationship checking, simply add a second level of complexity that
should be addressed in a single repository of business rules.

Joe