Re: foundations of relational theory?

cmurthi wrote:

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

One classification of integrity constraints is the following;

1. domain (or data type) constraints
2. column constraints
3. table (or relation) constraints
4. database constraints
5. just means that the value of the column must abide to the data type (e.g. integer, date, etc...). This datatype could be very complex
6. would be an extra constraint on a spesific column (e.g. qty integer constraint(qty>=0) )
7. would be a constraint that applies to the rows of one table. Eg. start_date <= end_date
8. database constraints would be constraints that apply to rows in all tables.

Is it possible to express all necessary rules just using declarative constraints? I have tested some of
these systems and it feels like a nice way to build applications. I have not made very complex ones,
however.

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

Triggers are, of course, available in nearly all SQL-databases.

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

However, that is not necessarily the case. There are products that know now to "migrate" rules to the client so that they get applied during data entry time. This is done automatically without any coding.

regards,
Lauri Received on Tue Oct 28 2003 - 23:10:09 CET