Re: Foreign Key - forcing at database side or appln side

In article <921j28$5qmjf$3_at_ID-62141.news.dfncis.de>,   "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:
>
> "Mark D Powell" <markp7832_at_my-deja.com> wrote in message
> news:91vu6j$d2r$1_at_nnrp1.deja.com...
> > In article <91usdq$k4v$1_at_nnrp1.deja.com>,
> > pradeep_jain_111_at_yahoo.com wrote:
> > > Hi all,
> > >
> > > Where's the best place to enforce foreign key constraints -
 database
> > > side or application side or at both places. (Only 1 appln uses
 this
> > > database).
> > > What are the issues if it's enforced only at the appln side, and
 the
> > > foreign key constraints are not created at the database side at
 all -
> > > issues wrt time for insertion, time for queries, possibility of
 losing
> > > database integrity, additional coding and queries required at the
> > > application side to enforce the constraints, etc.
> > >
> > > Thanks.
> > > Pradeep
> > >
> > > --
> > > Life can always get better...
> > >
> > If constraints are stored in the DB then:
> > advantages
> > 1) if the application programmer forgets or does not know about the
> > rule the database will still protect the data
> > 2) the db is usually very efficient at checking the constraints,
> > usually more so than the application
> > 3) PK, UK, and FK defined in the database serve as a form of
> > documentation about the relationships between the tables
> >
> > disadvantages
> > 1) the error message returned to a user is pretty ugly (though the
> > program can check for and format user friendly error messages)
> > 2) where the rule is to only be selectly enforced you usually need
 to
> > do it in the application
> > 3) FK's make table/index maintenance slightly more difficult for the
> > DBA (You can not drop and rebuild a PK index in another tablespace
 if
> > there are 5 FK that reference it without first disabling the FK's,
 and
> > then you must remember to re-establish the FK after rebuilding the
> > index.)
> >
> > There may be other considerations but these are what comes to mind
> > right now.
> > --
> > Mark D. Powell -- The only advice that counts is the advice that
> > you follow so follow your own advice --
> >
>
> Your point 3 doesn't hold anymore.
> You CAN rebuild a PK by issuing alter index <pk> rebuild
> WITHOUT manipulating the associated FKs
>
> This applies to 7.3.3 and onwards
>
> Regards,
>
> Sybrand Bakker, Oracle DBA
>

True.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com
http://www.deja.com/