Path: utzoo!mnetor!uunet!lll-winken!csustan!polyslo!lchirica From: lchirica@polyslo.UUCP (Laurian Chirica) Newsgroups: comp.databases Subject: Referential Integrity Constraints (Long) Message-ID: <1342@polyslo.UUCP> Date: 27 Feb 88 07:25:35 GMT Organization: Cal Poly State Univ,CSC Dept,San Luis Obispo,CA 93407 Lines: 95 Keywords: Referential Integrity, Triggers, SYBASE, UNIFY --- 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