Re: Declarative constraints in practical terms

mAsterdam wrote:
> dawn wrote:
> > mAsterdam wrote:
> >
> >>dawn wrote:
> >>
> [snip]
> >>>5) In the case of declarative constraints, they are necessarily
> >>>employed by any application writing to the database. In the case of
> >>>metadata + code, each organization must determine whether and how to
> >>>technically enforce business rules for all applications or enforce them
> >>>through standards and QA approaches.
> >>
> >>One programmer error can ruin your data. No QA can prevent that.
> >
> >
> > One (dba) programmer can damage data in either case.
>
> Yes. However, there is still this crucial difference:
> Once correct constraints are enforced in the database,
> it is not possible for a programmer to violate them by mistake.
> The dbms does not do the update it was told to do, and
> instead gives a warning. In the case where the validation is
> done outside the database, programmer errors still may
> (and will) ruin your data.

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.

> > In the one case
> > more damage can be done by those writing the database services while in
> > the other by those writing database constraints. I don't know if one
> > of these is harder to get right, harder to debug, or harder to include
> > test cases for in regression testing. Do sites often put their SQL
> > constraint test cases into the same QA test harness as their OO or
> > procedural code?
>
> This is not primarily a QA issue.
> It is about protecting valuable assets.

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

> >>>6) If there are local constraints (constraints for one application and
> >>>not another) then these are either declared in SQL as local constraints
> >>>(something I've read about, but never used) or, often, coded only in
> >>>the individual applications using metadata + code. So, local
> >>>constraints are often handled the same but in one case local and global
> >>>constraints are coded in the same language, typically by the same team.
> >>>
> >>>I lean toward not duplicating constraints, coding and maintaining them
> >>>in multiple places and languages, but I understand that someone else
> >>>might choose the other strategy. Whatever choice, it doesn't look
> >>>obvious to me that declarative constraints are better as I gather it
> >>>appears to many others.
> >>
> >>I feel that there are two issues mixed here:
> >>decalative vs. procedural and
> >>inside vs. outside the scope of the dbms.
> >
> >
> > Yes, I'll buy that. They are related, however.
>
> In practice, maybe - in theory - dunno .. to me it feels
> better to treat them as sepearate issues.

OK. Cheers! --dawn Received on Wed Feb 22 2006 - 20:17:49 CET