Re: foundations of relational theory?

From: cmurthi <xyzcmurthi_at_quest.with.a.w.net>
Date: Tue, 28 Oct 2003 16:07:57 -0500
Message-ID: <3F9EDAAD.6030907@quest.with.a.w.net>

Lauri Pietarinen wrote:

> Jonathan Leffler wrote:
> 

>> cmurthi wrote:
>>
>>> Marshall Spight wrote:
>>>
>>>> I'd still argue that a declarative integrity enforcement
>>>> system is better than a procedural one.
>>>
>>>
>>>
>>> Can a declaration enforce a really complex oonstraint? And if so,
>>> does the declaration begin to look like a procedural statement? For
>>> example, even on a simple level, how would you declare that Field1
>>> has to be conditionally based on Field2 and Field3, eg, procedurally,
>>> if(field3=='') field1=field2 else field1=field3.
>>
>> Using the Informix dialect of SQL, and ignoring SQL NULL values (since
>> they make a mess of everything), you could do:
>>
>> ALTER TABLE WhatNot ADD CONSTRAINT ((field3 = '' AND field1 = field2)
>> OR (field3 != '' AND field1 = field3));
>>
>> You could give the constraint a name - though ISO SQL and Informx
>> manage to do that differently. You could also include that
>> declarative constraint in the CREATE TABLE statement.
>>
>> I don't regard that as a particularly complex constraint. I'm not
>> sure that it is part of a good database design, but that is probably
>> simply because it is illustrative rather than anything else.
>>
>> The big advantage of the declarative constraint is that the DBMS
>> enforces it. No application can violate it, whether accidentally or
>> on purpose. If the constraint becomes obsolete, then you drop it.
>> Altering it is a two-stage drop and add operation (which Informix
>> permits you to do in a single ALTER TABLE statement, so there is no
>> window of vulnerability while you are changing the constraint).

Thanks, Jonathon. Firstly, the constraint syntax is reminiscent of old scripting languages; field = '' [then] field != '' [then]... I am assuming this is a common syntax. Procedural syntax is simply more elegant, but that's not a functional comment, it's an aesthetic one.

More to the point, the more complex the constraint (and I was giving only the simplest above,) won't it become, simply, unreadable? And what if you wanted the constraint to be be dependent on "foreign" data (I think you call it), ie: if(sum(values_from_another_table if present for this record key) < 0) [ok] elseif (something else..) etc. I see these as valid "constraints" in that, while maybe infringing on business rule territory, they should still be enforced at the lower level as part of the db set.

Procedurally, this would be easy; in Pick we would encode the rules (in PickBasic, like it or not,) in a file-write-time trigger subroutine. Whether or not it's an automatic trigger; if it's automatic, it could not be violated by errant application programmers or other non-data-entry methods of update.

> The problem with constraints like these is that, yes, you safeguard your 
> db from corrupt data, but getting the information back into the 
> application is not that easy:  you have to parse the error message, get 
> the name of the constraint and translate it into a meaningfull error 
> message for the user.  I don't know if anybody does this, so, in effect 
> you end up coding the same checks twice:  in the app and in the db.
> 
> This is where we run into the frustrating "mismatch" between app 
> language and db environment.

That is the main problem of centralizing the constraints; you would have no user-feedback until submit of the entire dataset, which is both frustrating and counter-productive, and, in fact probably unacceptable to users (or, I should say, used to be; web-based data entry is inuring us to nasty messages at submit time instead of at data-entry time.) So, as you say, you check once at dataentry time and again (perhaps more robustly,) at dbms time.

Chandru Murthi

> regards,
> Lauri Received on Tue Oct 28 2003 - 15:07:57 CST