Re: Declaring Unenforced Constraints

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Thu, 18 Nov 2004 18:42:07 -0500
Message-ID: <fjov62-64f.ln1@pluto.downsfam.net>

Tony Andrews wrote:

> Kenneth Downs wrote:

>> So, to get back to the original thread.  The solution was to add what

> we

>> called "OK TO FAIL" to all constraints.  What this meant was that the

> build

>> would not abort on a constraint create failure, it would write it as

> a

>> warning and keep going.  This allowed us to get to 100% by degrees,

> instead

>> of in a big lurch.
>>
>> They eventually got fixed, mostly.  At this shop the CEO would

> ocassionally

>> show up with somebody and say, "this is my friend Johann Schmitt,

> perhaps

>> you have a spot for him?"  What do you know!  We did!  Eventually one

> of

>> these FOC's (friend of CEO) actually had some SQL skills, and we put

> her on

>> to running SELECTs to find offending data and working with the client

> to

>> manually purge out the violations.  Then we'd run an upgrade pass,

> which

>> would notice the constraints were not there and would try to put them

> back

>> in, and would finally succeed.
>>
>> In the end, the bigger clients all had their legacy constraints in

> place and

>> new improvements came in with the constraints in place from day 1.

>
> This sounds very similar to the position my client is currently in.
> They are a software house with a product that has many implementations
> at their customer sites. The Oracle database design pre-dates the
> ability to declare constraints in the database, and although primary
> and foreign keys have been added there are few check constraints. One
> core table represents about 15 subtypes of an entity, each with its own
> "rules" that are embedded in the application code. Frequently, new
> code is written by developers that violates one or more of these rules,
> but in subtle ways that are not caught in system testing and only cause
> problems to some or all customers some months later. By this time, the
> data error is so entrenched that it is hard to write a data fix script
> to put things right (rather like trying to turn a cake back into its
> ingredients to make a different cake). Also, the data fix scripts are
> quite capable of introducing yet more errors of a similar nature!

Perhaps someday I'll convince you of how much easier that is to handle when the tables are ruthlessly normalized, so that much more checking can be done with key constraints.

>
> I am advocating adding constraints to enforce the rules in the
> database, so that bad code gets caught immediately and data fixes are
> not required. However, I have to recognise that this is a non-trivial
> undertaking, because:
>
> 1) probably every customer has some data that violates the constraint,
> even if it is historic data that will never be processed again. So the
> "ENABLE NOVALIDATE" option will need to be used in most cases (unless
> it is considered worth while fixing the data at every customer site at
> the same time as implementing the constraint).

I don't know about Oracle, but AFAICR in SQL Server you can also declare constraints w/o enforcing them on existing data. They are enforced, as we like to say these days, "on a go-forward basis."

>
> 2) extensive regression testing will be required, because it is highly
> likely that various parts of the application will be found to violate
> the constraint under some circumstances. Even then, we must be
> prepared that some customer will hit a constraint violation that out
> regression testing missed.

Yikes. Do you have extensive regression testing now? Unless you can make it completely automatic....

>
> It is early days yet, and I am wondering whether to suggest we go for a
> "big bang" approach where we implement tons of constraints at once,
> regression test like mad, and then wait for the screams; or a drip-feed
> where we implement a smaller number of constraints with each upgrade,
> regression test like mad, and hopefully get less issues, spread over a
> longer time frame.

In my experience drip-feed will more likely be accepted by the more people, giving you a greater overall good. Losing the fight for big-bang brings zero improvement, but going one-by-one gives you concrete and permanent improvements that will never cause trouble again. I wish you luck.

-- 
Kenneth Downs

java.lang.String.tcpip.usenet.posting.response.sigblock.setSig("After
finally finding the right object abd method to set the sig block, I forgot
what I wanted to say!");

Received on Thu Nov 18 2004 - 17:42:07 CST