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

"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 --
>
>
> Sent via Deja.com
> http://www.deja.com/

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 Received on Sat Dec 23 2000 - 01:10:01 CST