Path: utzoo!mnetor!uunet!lll-winken!csustan!polyslo!lchirica
From: lchirica@polyslo.UUCP (Laurian Chirica)
Newsgroups: comp.databases
Subject: Re: Informix 4GL Question?
Message-ID: <1320@polyslo.UUCP>
Date: 23 Feb 88 21:03:36 GMT
References: <714@uel.uel.co.uk> <2314@geac.UUCP>
Reply-To: lchirica@polyslo.UUCP (Laurian Chirica)
Organization: Cal Poly State University -- San Luis Obispo
Lines: 59
Keywords: Informix 4GL

In article <2314@geac.UUCP> daveb@geac.UUCP (David Collier-Brown) writes:
>In article <714@uel.uel.co.uk> andrew@uel.uel.co.uk (Andrew Josey) writes:
>>When deleting records in my database I check whether they are
>>referenced by any other records. This is currently done by code
>>similar to the following :
>>
>>let counter = 0
>>select count (*) into counter from table
>>  where table.column = key
>>if counter > 0
>>...
>>As some of my database tables are large, I would like to find
>>an alternative technique that terminates after finding the first
>>match instead of searching the whole table.
>
>   This is interesting, in that it is a perfectly plausible request
>that does not **seem** to be well-defined in the relational
>algebra/calculus (and perhaps even less well-defined in the *#%!@?&&
>sublanguages we get with them).
>

I never worked with INFORMIX but if I remember correctly, it has
"cursors" which allow the type of processing you are asking for:

/* Suggestion --This is a schetch of the code involved */
/*  this code would be embedded in a Pascal or C program */

 define cursor X as select <anything>
	            from <relations> 
		    where <qualification>

 open cursor X

 while there are tuples left
      fetch cursor X    /* that is successive tuples that */
                        /* satisfy <qualification> */
      if I_want_to_quit then break else continue
end while 
/* end of suggestion */

P.S. Referring to your example "select count(*) .... etc., you seem
to believe that the system will scan the entire relation in order
to processes the query.  I do not know enough about INFORMIX but
any relational DBMS worth paying for will NOT scan the entire relation
to get the answer, IF "key" in your text is a primary key.  Any reasonable
query processor will do a keyed retrieval on "key" and return a 0 or 1
as an answer without scaning the relation.

P.P.S  The code in your example performs what is known in relational
database theory as a referential integrity check.  There are systems
(e.g., UNIFY) that will do that check automatically for you.

TO THE NET PEOPLE:  Does anyone know what other DBMSs support
referential integrity?

-- 
Laurian M. Chirica
Computer Science Department
California Polytechnic State University (CAL POLY)
San Luis Obispo, CA 93407 - (805) 756-1332