Re: foundations of relational theory?

From: Jonathan Leffler <jleffler_at_earthlink.net>
Date: Tue, 28 Oct 2003 17:24:14 GMT
Message-ID: <2Dxnb.5422$Px2.3805@newsread4.news.pas.earthlink.net>

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

-- 
Jonathan Leffler                   #include <disclaimer.h>
Email: jleffler_at_earthlink.net, jleffler_at_us.ibm.com
Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/

Received on Tue Oct 28 2003 - 11:24:14 CST