Re: Declarative constraints in practical terms

dawn wrote:
>
> If the database services are used by application developers, then
> couldn't inside-the-database constraints be botched up by developers
> (e.g. dba's) just like outside-the-database services could be bothed up
> by developers (e.g. database service/infrastructure programmers)? As
> long as applications use whatever framework is in place for maintaining
> the database, then the constraints will be applied. In either case
> someone has the ability to botch things up, whether via the dbms
> specifications or database services code.

I think you're asking what is the difference between

(a) posting the constraints in the DBMS and having applications interact directly with the DBMS and

(b) coding the constraints in a wrapper around the DBMS and having all applications interact indirectly via the wrapper?

I can think of several reasons why (a) is preferable to (b):

CORRECTNESS
In (a) the constraints can be expressed declaratively and directly (i.e., ideally with nothing more than a syntactic reformulation of the specification of the constraints) to the DBMS, whereas in (b) the constraint specification must be converted into some (probably non-declarative) programming language. This is an extra opportunity to introduce bugs, design errors, and performance problems that simply cannot arise in (a).

EFFICIENCY
In (a) the DBMS can optimise the checking of constraints in a way that cannot be done by (b), which must treat the DBMS as a black box.

DEVELOPMENT AND MAINTENANCE
Option (b) necessarily means
trying to write high performance parallel code, which is notoriously hard to get right (not to mention dealing with replication, distribution, recoverability, and so forth). The DBMS code, on the other hand, has already solved these problems in a generic fashion and been well tested.

TRUST
The constraint language used in the DBMS (a) is going to have seen much more testing and use than the code developed in the roll-your-own approach in (b).

APPLICATIONS
Application programmers will be coding to an industry standard interface in (a). I expect it would also be much easier to interface other tools and products using (a) rather than (b).

> I have not worked in a shop that fully utilized SQL constraint
> handling, but presumably constraints need to be added, changed,
> removed. This would introduce a risk that would be tested before being
> deployed, right? That is what my question was about. I don't know if
> new builds of the software run through regression tests for the dbms
> constraints as well as application code or not, typically (of course
> I'm sure there is variety).

There is no way to fully protect against human error, the best one can do is try to minimise the number that go undetected. The trouble with plan (b) is that it has so many more sources of trouble than (a). If you
like, (a) has fewer moving parts.

• Ralph