Re: foundations of relational theory?
Date: Wed, 29 Oct 2003 01:06:16 GMT
Message-ID: <coEnb.57051$pT1.53145_at_twister.nyc.rr.com>
"cmurthi" <xyzcmurthi_at_quest.with.a.w.net> wrote in message
news:3F9EDAAD.6030907_at_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.
In Standard SQL, one could also use a CASE, or conditional, expression to specify the constraint, e.g.,
CREATE TABLE T
(
s1 CHAR(5) NOT NULL, s2 CHAR(5) NOT NULL, s3 CHAR(5) NOT NULL,
CHECK (s1 = CASE WHEN s3 = '' THEN s2 ELSE s3 END) )
> 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.
In Standard SQL, a declarative integrity constraint can be specified by a subquery so the full expressiveness of a SELECT expression is available. For example, let's assume that we're representing securities transactions. Such a transaction must be settled, by paying for a purchase or by delivering a sold asset, within three business days, for example. Obviously, abiding by the applicable business calendar, e.g., the NY Stock Exchange calendar in the US. So we might have the following basic representation (just a sketch to illustrate declarative integrity constraints by subquery):
- A table of NYSE business dates
- Consecutive business dates are associated with consecutive natural numbers CREATE TABLE NYSECalendar ( business_date DATE NOT NULL PRIMARY KEY,
- maintain constraint that consecutive business dates be mapped to consecutive
- natural numbers date_count INT NOT NULL UNIQUE CHECK (date_count = COALESCE((SELECT C1.date_count FROM NYSECalendar AS C1 WHERE C1.business_date = (SELECT MAX(C2.business_date) FROM NYSECalendar AS C2 WHERE C2.business_date < business_date)) + 1, 1)) )
- A trade is an agreement on a date between a buyer and a seller to exchange an
- asset, i.e., a security, for payment and to settle by a future date, e.g., 3 days
- hence. CREATE TABLE Trade (
- assume foreign key constraints are placed where applicable trade_id INT NOT NULL PRIMARY KEY, buyer_id INT NOT NULL, seller_id INT NOT NULL, cusip CHAR(9) NOT NULL, -- security identifier payment DECIMAL(12, 2) NOT NULL, trade_date DATE NOT NULL REFERENCES NYSECalendar (business_date), settlement_date DATE NOT NULL REFERENCES NYSECalendar (business_date) CHECK (settlement_date BETWEEN trade_date AND (SELECT C2.business_date FROM NYSECalendar AS C1 INNER JOIN NYSECalendar AS C2 ON C1.business_date = trade_date AND C2.date_count = C1.date_count + 3)) )
> 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.
In a DBMS that doesn't allow subqueries as declarative constraints, such as SQL Server, then triggers can be defined for the same effect. Triggers are procedural objects, basically like stored procedures just invoked automatically, but they do reside in the DBMS.
Regards,
jag
> > 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 Wed Oct 29 2003 - 02:06:16 CET