Path: utzoo!attcan!utgpu!jarvis.csri.toronto.edu!mailrus!tut.cis.ohio-state.edu!ucbvax!pasteur!dean.Berkeley.EDU!pnnbkr From: pnnbkr@dean.Berkeley.EDU (=steven pennebaker) Newsgroups: comp.databases Subject: Re: Ingres and referential integrities Keywords: Ingres referential integrities Message-ID: <15945@pasteur.Berkeley.EDU> Date: 31 Jul 89 05:59:25 GMT References: <3081@rti.UUCP> <3225@rtech.rtech.com> <9677@alice.UUCP> <3236@rtech.rtech.com> Sender: news@pasteur.Berkeley.EDU Reply-To: pnnbkr@dean.Berkeley.EDU.UUCP (=steven pennebaker) Distribution: comp Organization: University of California, Berkeley Lines: 81 >> Article 3431 of comp.databases: >> Path: pasteur!ucbvax!decwrl!sun-barr!apple!oliveb!amdahl!rtech!squid!robf >> From: robf@squid.rtech.com (Robert Fair) >> Newsgroups: comp.databases >> Subject: Re: Ingres and referential integrities >> Date: 27 Jul 89 12:41:50 GMT >> Organization: Relational Technology, Inc. >> >> >From: debra@alice.UUCP (Paul De Bra): >> > >> >Well, it doesn't look good if RTI's technical support doesn't even know >> >what referential integrity means, does it? >> > >> Actually, I know very well what referential integrity means, and so do >> many others at RTI ! who are they? what are their names?? :-) >> >> The essential point of referential integrity is that the DBMS does not >> allow users to violate the integrity (e.g. by writing an ESQL program). >> There are two basic ways to do this (both implemented in the DBMS): >> >> - Passive (rules/triggers). This allows users to do any kind of update, >> with rules being fired when update occurs to perform the associated >> referential checks, cascaded updates etc. Rules are very nice, but >> can be complex to setup and maintain (how about cascaded rules ? >> recursive rules ? etc) >> >> - Active (dbprocedures). This defines one or more well-defined interfaces >> to update sets of tables, complete with error checking, transaction >> control etc to enforce referential integrity. Database procedures may >> not be as transparent/elegent as rules but they provide just as >> much data integrity, and are often easier to set up/manage for >> real applications. >> this is a good summary. the point that i think is worth making, and seems to be the underlying assumption of yogesh gupta's article 3424, is that in most cases simple cascade/restrict/nullify rules suffice for maintaining referential integrity. database procedures have advantages, but seem like an awful tedious way of solving a problem that can be modeled so much more easily. my solution to the problem of enforcing referential integrity in ingres rel. 5 has been to develop a single foriegn key table that is read by routines that enforce the rules. it is simple, centralized, easily managed and changed. in fact, given triggers ala sybase, i'll still use the table to generate the triggers! i suppose you could gen the procedures, but it still seems like the long way around. in one database that has ~160 delete rules in it, i found 3 cases where the rules alone were not sufficient. in 2 of those cases, i cheated and decided that rather than cascading i should restrict which did work out and the last case, which really needs to be a cascade, could be (but won't be) resolved w/ a bit of normalization (and subsequent degredation in reporting performance!). i'm not certain how database procedures allow you to avoid tricky cascade/recursion problems as you suggest above. regardless of whether you choose a rule based approach or a db procedure approach, you will still have to work out the consequences of a transaction. personally, i'd like both triggers and database procedures. triggers because they're easily managed, simple and, given a well designed database, usually all you need, and database procedures because every now and then you've gotta do something odd and that's a good way to handle it. >> The choice between active and passive often depends on personal preference, >> neither is a perfect solution for all possible cases. and they call it computer "science"! :-) >> >> Robert Fair >> Technical Support >> Relational Technology steven pennebaker disclaimer: the opinions are mine!