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

From: internetmaster <youlove_at_me.com>
Date: Fri, 26 Jul 2002 22:20:58 -0400
Message-ID: <3D42038A.2040904@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=a20d28ee.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

Received on Fri Jul 26 2002 - 21:20:58 CDT