Re: Performance of constraints

>If you don't need to enforce parent rules but only need to ensure that a parent >exists at the time a child is inserted/updated then I suggest using a trigger >rather than a referential constraint. If the parent rules need to be enforced >then you must either have the referential constraint or you can use a trigger. > The only advantage of a trigger is that it may be able to use an existing >performance index of combined attributes where the first attributes of the >index are the same and are in the same order as the foreign key. A referential >constraint will build its own index rather than use this index which is a >reported bug. > >You can use ERwin to help you build these referential checking triggers. > >Cheers - Jim > >-- >----------------------------------------------------------------------------- >Jim Gordon DHL Airways Inc. jgordon@us.dhl.com >----------------------------------------------------------------------------- >My opinions are my own. They may vary with time but they remain mine! Forgive me if this doesn't address the primary question, but I thought I would add it to the discussion of RI. There are at least three reasons to NOT create referential integrity via create table statements as ERWIN does. First, if you explicitly create the indexes, and then add the constraints, the existing indexes will be used. Just because you may want to drop RI at some point doesn't mean that you necessarily want the indexes to go away as well. Indexes will be dropped with the constraint if they were created implicitly. :-( Second, should you fragment your tables, you will probably want to create your indexes explicitly and even detach them from the table. You can't do that implicitly. Third, implicitly created indexes cannot be altered to cluster. Michael Reed, Radiix Inc., Ann Arbor, MI mreed@radiix.com