Re: FOREIGN KEY constraint

In article <2pirep$e6c_at_dockmaster.phantom.com> ynp_at_phantom.com (Youri Podchosov) writes:
>Does anybody have anything to say about table/column level constraint
>FOREIGN KEY? Any warnings/observations/cautions? How does it compare to
>database triggers used for the same purpose?
>
>The reason for these questions is: we're gonna redefine (I mean ALTER)
>the most part of legacy database to enforce referential integrity by
>means of ORACLE itself, so although I personally don't see any reasonable
>objections to such a modification, not everybody is 100% sure about it.
>
>Thank you in advance for your time.
>
>___
>YNP
I can't speak about performance issues or issues like being able to disable integrity during data loading. Hopefully some DBAs out there will help out with this information.

What I *can* tell you is that declarative referential integrity is very desirable for improving productivity of application developers, reducing integrity issues related to application development errors or schema changes over time, and for easing the task of end user database query and reporting.

With declarative referential integrity, intelligent client/server tools like Oracle CDE's 4GL's (Oracle Forms, Oracle Reports, and Oracle Graphics) can read the database system catalog tables/views and use key information to generate code for joining, synchronizing, and integrity enforcement between tables, as well as make intelligent layout decisions and intelligent decisions about "pre-enforcement" of integrity conditions and domain constraints on the client. For example, a constraint that a "ship date" column must contain a date value greater than or equal to the "order date" column in the same table can be checked on the client, rather than wasting network traffic, user time, and server CPU/disk time discovering something that the client tool could have easily avoided. Similarly, declarative integrity constraints can be used to ensure that "children records" don't get orphaned when their "parent" is deleted, either on the client side or on the server side.

CASE tools can also read the database system catalogs to reverse engineer schemas that were not generated from their repository.

End user tools can do automatic joins and load related tables, reducing training requirements, reducing the need for schema reference information, and even be used to suggest related data that may be of interest during the creation of a new query.

Anyway, I hope that this is helpful in your decision. Good luck!

• Dennis Moore, my own opinions, etcetcetc