Re: Referential Integrity Enforcement: Do With Constraints or Triggers?

"internetmaster" <youlove_at_me.com> wrote in message news:3D42038A.2040904_at_me.com...
> Sybrand Bakker wrote:
> > On Fri, 26 Jul 2002 17:55:57 -0400, internetmaster <youlove_at_me.com>
> > wrote:
> >
> >
> >>In an earlier thread I mentioned the following observation:
> >>
>
>>http://groups.google.com/groups?dq=&hl=en&lr=&ie=UTF-8&oe=UTF-8&threadm=a2
0d28ee.0207260158.3ecabf94%40posting.google.com&prev=/groups%3Fhl%3Den%26lr% 3D%26ie%3DUTF-8%26oe%3DUTF-8%26group%3Dcomp.databases.oracle.tools
> >>
> >>
> >>I was wrong about one thing. The database does have primary keys
> >>defined, but no foreign keys. I went browsing through the data
> >>dictionary and found that the database has a ton of Triggers. It appears
> >>they're using Triggers to enforce the referential integrity rather than
> >>using constraints.
> >>
> >>They have Insert triggers on the parent tables that handle cascade
> >>deletes and prevent illegal inserts on child tables where they key value
> >>on the parent table doesn't exist.
> >>
> >>My question is it less expensive to handle the RI this way? I can see
> >>where creating a ton of constraints would get to be cumbersome and
> >>perhaps Triggers would be easier from a maintenance perspective.
> >>
> >>For example, there is one parent Table (Project) that has about 15 child
> >>tables associated to it logically. The developers of this application
> >>created 2 triggers to handle the Insert and Delete anomalies whereas 30
> >>constraints would had to have been coded.
> >>
> >>From a performance perspective, what are the ramifications for this
> >>design decision? How expensive is a constraint compared to a Trigger?
> >>
> >>
> >>Thanks.
> >
> >
> >
> > I have worked with several applications implementing the RI by means
> > of triggers. We tested and confirmed implementing RI by means of
> > constraints is about 8 times cheaper in terms of performance.
> > Constraints don't use explicit sql to check RI, they are using
> > internal mechanisms, triggers do use explicit sql.
> > Your observations are incorrect, and I doubt whether those 2 triggers
> > deal with all anomalies.
>
>
> As far as I know, my observations are correct. When I run the following
> query for the application's schema, I don't find any referential
> constraints defined. Am I missing something?
>
> select distinct constraint_type
> from user_constraints
>
>
>
> But thanks for the info on the cost difference. That's exactly what I
> was looking for. I rarely ever hear anyone talk about things like that.
>
> >
> > Regards
> >
> > Sybrand Bakker, Senior Oracle DBA
> >
> > To reply remove -verwijderdit from my e-mail address
>
>

What I meant is: I don't believe 2 triggers are sufficient to check anomalies for 15 tables. The triggers are probably checking parent-child relations, they are not checking children - parent relations. Normally, every individual table needs to have it's own triggers to check RI.

Hth

--
Sybrand Bakker
Senior Oracle DBA

to reply remove '-verwijderdit' from my e-mail address