Re: Declaring Unenforced Constraints

From: Kenneth Downs <firstinit.lastname_at_lastnameplusfam.net>
Date: Wed, 17 Nov 2004 08:51:11 -0500
Message-ID: <fj1s62-dhi.ln1@pluto.downsfam.net>

Tony Andrews wrote:

> Kenneth Downs wrote:

>> As a late response, may I ask if you perhaps meant delayed

> enforcement? As

>> in loading lots of data and then running some type of batch

> enforcement

>> later on?  Or do you mean a constraint that is not enforced at all

> ever?

>>
>> In the first case, it seems that we are not so much un-enforcing as

> we are

>> redefining a transaction.  There are certain tables I have for a

> batch

>> operation, which have no declared constraints on them, though they do

> have

>> many unique and referential constraints that must be correct or I

> have

>> garbage going in.  The lack of declared constraints allows the data

> to be

>> loaded in any order, such as children before parents.  Just before it
>> starts processing, it validates the entire set and reports all

> errors, if

>> there are any.  In this sense the transaction is considered all

> activities

>> up to the request for processing.
>>
>> Of course we'll need to hear from Tony on how this creates Dirty

> Reads in

>> Oracle ;)

>
> Impossible - Oracle is incapable of performing a dirty read (hurrah!)
>
> Deferred constraints allow insert of children before parents: the
> constraint checking is only done at the end of the transaction.
>
> Of course, for bulk data loading it may well be useful to disable the
> constraints, load the data, and then re-enable them (after fixing any
> problems). But the database must be off-line to normal users while
> this happens, otherwise they may see bad data (a dirty read of sorts, I
> suppose). ^^^^^^^^^^^^^^^^^^^^^^^^

This was the case I had in mind when I mentioned your name. If the database is left online and constraints are deferred but the bulk load is not wrapped in a transaction, you have bad data coming in. Even if it is somehow rejected later, users would see it in the meantime. Hence it seems we are talking not so much about whether to enforce constraints as we are talking about when to enforce them. From here comes my conclusion that we are really talking about constraints and transactions.

Bringing the db offline does not seem very nice, must be a better way than that.

-- 
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 Wed Nov 17 2004 - 07:51:11 CST