Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Usenet -> c.d.o.server -> Re: Referential Integrity Enforcement: Do With Constraints or Triggers?

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

From: Niall Litchfield <>
Date: Sat, 27 Jul 2002 21:03:55 +0100
Message-ID: <3d42fcae$0$8514$>

"internetmaster" <> wrote in message
> >>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.

Sybrand and Marc have pointed you towards the performance disadvantage of this approach. How do you handle data loads/feeds from other systems to the child tables. The trigger logic will be bypassed. People often do this for one of three reasons

  1. They assume the data will only ever be accessed by a given application. If on the other hand you went to your marketing people and said 'I'm sorry the data in our order-entry system is only valuable in the context of the order-entry system so you can't interact with it' I suspect they'd soon point out the error of this assumption. Similarly if you were to say - sorry we can't open up that new distribution channel because the app can't handle it the MD/CEO might having something to say.
  2. they are software suppliers and/or consultants and so they know that if you want to 'leverage the power of your investment' (read use the data another way). You'll have to buy tools or consultancy from them because all the data integrity rules are in the app.
  3. They want a cross-platform product that doesn't care about the platform it runs on. So long as they are happy that this equates to they don't care about performance then this argument holds up just fine.
Niall Litchfield
Oracle DBA
Audit Commission UK
Please include version and platform
and SQL where applicable
It makes life easier and increases the
likelihood of a good answer
Received on Sat Jul 27 2002 - 15:03:55 CDT

Original text of this message