lchirica@polyslo.UUCP (Laurian Chirica) (02/27/88)
--- This article could not take any more nesting ------- In article <3489@cup.portal.com> DMasterson@cup.portal.com writes: >In message <1320@polyslo.UUCP> lchirica@polyslo.UUCP writes: >>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. >> ..... >>P.S. Referring to your example "select count(*) .... etc., >> ... >>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. >Not entirely true. If "column" is any indexed field in the table, then any ^^^^^^^^^^^^^ Sorry about the misunderstanding. I thought that we are talking about "keys" not just any field. Please note the IF clause in my text. >>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. >What referential integrity check?!? (I assume you deleted that code from the >above message). Also, what is your definition of a referential integrity >check? I didn't know that Unify really handled such checks. I knew they do >validity checks within their forms management system, but can they use this >mechanism to really check other tables for verification of the update? Yes, UNIFY has what they call "explicit references" built-in into their database schema facility. Any attempt at deleting a tuple refereced by another tuple will be rejected by the DBMS, *not* by the form management system (i.e., ACCELL/IDS). For example, if I declare an "explicit relationship" between an EMPLOYEE relation and a DEPARTMENT relation, I cannot delete any department tuple, if it is currently referenced by any employee tuple. Similarly, on insertion or update I cannot add/update an employee if the refrenced department tuple does not exist. All this is done without having to write any line of application code. >can this validity check be used to update other tables based on the current >update to the current table .... No, it cannot. UNIFY does not have "triggered updates", thank goodness! Some 10 years ago I wrote code for a CODASYL DBMS (which had triggered or cascaded updates) and I manged to destroy the database. I spent one whole night putting it back together. I still have occasional nightmares. All I want from an integrity checking mechanism is an indication that the application code has violated a constraints. The code then should deal with the situation as it sees fit, within protection limits, of course. >>TO THE NET PEOPLE: Does anyone know what other DBMSs support >>referential integrity? >> >The only "well-known" system that I have seen that supports referential >integrity is the Sybase DataServer. It does this through triggers that are ^^^^^^^^^^^^^^^^ Triggers are a great!. They allow a far greater degree of control over the database integrity. Of course they can easily deal with referential integrity constraints. (Also, if anything goes wrong with my code I can blame it on whomever wrote the trigger :-). I have a very high opinion of SYBASE although I am not fortunate to have access to it. >..... My familiarity is >with mini- and micro- system databases, there may be other systems for the >mainframe world (SQL/DS don't count). ^^^^^^^^^^^^^^^^^^ Why? SQL/DS and DB2 are direct descendents of System R and they invented triggers (among other things). PS. This group is getting back to life! Or so it seems. -- Laurian M. Chirica Computer Science Department California Polytechnic State University (CAL POLY) San Luis Obispo, CA 93407 - (805) 756-1332
DMasterson@cup.portal.com (02/29/88)
In message (1342@polyslo.UUCP) lchirica@polyslo.UUCP writes: >--- This article could not take any more nesting ------- Well here we go again (<grin>). >In article <3489@cup.portal.com> DMasterson@cup.portal.com writes: >>In message <1320@polyslo.UUCP> lchirica@polyslo.UUCP writes: >>>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: [The line eater was here] >>> ..... >>>P.S. Referring to your example "select count(*) .... etc., >>> ... >>>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. > >>Not entirely true. If "column" is any indexed field in the table, then any > ^^^^^^^^^^^^^ >Sorry about the misunderstanding. I thought that we are talking about "keys" >not just any field. Please note the IF clause in my text. > Whoops, I forgot some database systems make the separation between primary keys and indexed fields. However, I think my point was worth making, although a little misdirected. I just made the cardinal error in relational systems of equating keys with indexed fields. >>can this validity check be used to update other tables based on the current >>update to the current table .... > >No, it cannot. UNIFY does not have "triggered updates", thank goodness! >Some 10 years ago I wrote code for a CODASYL DBMS (which had triggered >or cascaded updates) and I manged to destroy the database. I spent one >whole night putting it back together. I still have occasional nightmares. >All I want from an integrity checking mechanism is an indication that >the application code has violated a constraints. The code then should deal >with the situation as it sees fit, within protection limits, of course. > Hmmm, seems I've heard this feeling about DBMSs before (<grin of remembrance>). I've always said that designing fully relational databases can be tougher than designing networked or hierarchical databases (they're all tough). Glad I asked these questions about Unify, though (forgot more than I thought I had in a year of not using it). >>..... My familiarity is >>with mini- and micro- system databases, there may be other systems for the >>mainframe world (SQL/DS don't count). > ^^^^^^^^^^^^^^^^^^ >Why? SQL/DS and DB2 are direct descendents of System R and they invented >triggers (among other things). > Actually, I can't really talk against (or for) SQL/DS -- I haven't used it. All I have seen is articles by Dr. Codd and others giving it low marks in relational capability (like in how it measures up against Codd's 12 rules or Date's 12 rules). >PS. This group is getting back to life! Or so it seems. > Maybe... Let's keep it going.... >-- >Laurian M. Chirica >Computer Science Department >California Polytechnic State University (CAL POLY) >San Luis Obispo, CA 93407 - (805) 756-1332 David Masterson DMasterson@cup.portal.com
cy@ashtate (Cy Shuster) (03/02/88)
I'm surprised you had to write code for a CODASYL DBMS to enforce cascaded deletes. In the IDMS implementation, at least, there are a robust set of set membership options which automatically perform many of those functions (e.g. ERASE PERMANENT on mandatory sets). As an aside, it's interesting that while dBASE Mac is not "purely" relational since relationships need to be predefined (even though they are implemented through simple foreign keys), it is exactly via this predefinition that we are able to support referential integrity. Without full support of domains, a DBMS must still have some kind of advance knowledge about what constraints to support. How are integrity rules defined in other DBMSs (save writing custom triggers)? --Cy-- dBASE Mac Development UUCP:...seismo!scgvaxd!ashtate!cy
lchirica@polyslo.UUCP (Laurian Chirica) (03/03/88)
In article <502@ashton.UUCP> cy@ashtate.UUCP (Cy Shuster) writes: >I'm surprised you had to write code for a CODASYL DBMS to enforce >cascaded deletes. In the IDMS implementation, .... No, I think I stated the opposite: I damaged a database due to cascaded deletes. That's why I am very uncomfortable with cascaded deletes/updates in relational databases. I do not know of any relational DBMS that performs cascaded deletes, but I have seen some proposals in various papers. As a "compromise" the syntax should differentiate between a simple delete request which is rejected if a referential constraint is violated (eg. UNIFY) and a cascaded delete which goes ahead and (potentially) empties out the whole database (eg. ???). -- Laurian M. Chirica (lchirica@polyslo.UUCP) Computer Science Department California Polytechnic State University (CAL POLY) San Luis Obispo, CA 93407 - (805) 756-1332
mjr@well.UUCP (Matthew Rapaport) (03/06/88)
In article <1401@polyslo.UUCP> lchirica@polyslo.UUCP (Laurian Chirica) writes: >... I do not know of any relational >DBMS that performs cascaded deletes... Supra from Cincom systems of Cincinati (sp?) will perform cascaded deletes from external views if instructed to do so. Supra is also one of the "relational" dbms that inforces relational integrity inside the directory and independent of indexes. It is not perfect in this regard however, the current release, for example will not allow a foreign key to be NULL even if that is a legal value from the business perspective. I understand the next release will allow such things... Matthew Rapaport mjr@well (ptsfa!well!mjr)
trogers%yosemite@Sun.COM (Tom Rogers) (03/15/88)
In article <1401@polyslo.UUCP>, lchirica@polyslo.UUCP (Laurian Chirica) writes: > > In article <502@ashton.UUCP> cy@ashtate.UUCP (Cy Shuster) writes: > >I'm surprised you had to write code for a CODASYL DBMS to enforce > >cascaded deletes. In the IDMS implementation, .... > > No, I think I stated the opposite: I damaged a database due to > cascaded deletes. That's why I am very uncomfortable with cascaded > deletes/updates in relational databases. I do not know of any relational > DBMS that performs cascaded deletes, but I have seen some proposals in > various papers. Although SunUnify does not perform cascaded deletes, Databrowse (an entity-relationship editor/browser written on top of the ERIC interface to SunUnify) will cascade deletes or updates upon confirmation. We find this to be a very useful feature. There have been no problems using it, and it has not corrupted any databases. SunUnify does not allow cycles in reference graphs. I could imagine potential problems with cascading operations when cycles could be defined in reference graphs. Note that of this writing, cascaded deletes and updates (with cycles) are being included in SQL2. >As a "compromise" the syntax should differentiate > between a simple delete request which is rejected if a referential > constraint is violated (eg. UNIFY) and a cascaded delete which goes > ahead and (potentially) empties out the whole database (eg. ???). If you could empty a database with a cascaded delete, you would most likely have a one-entity database. Otherwise, you would have a database admin without a job if it happened more than once :-).