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

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/