Re: FOREIGN KEY constraint

From: Glenn Nicholas <Glenn_at_wplace.demon.co.uk>
Date: Wed, 4 May 1994 11:39:26 +0000
Message-ID: <768051566snz_at_wplace.demon.co.uk>


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.

Perhaps an issue you should keep in mind is that integrity constraints are essentially checked on commit. If a user enters a value which will fail an integrity constraint, it is often nice to tell them then and there rather than waiting for a commit to occur. The same goes for database triggers. The only way to provide field level validation messages (and this applies to other types of constraints, not just foreign key constraints) is to code it in the form. This is a tricky area, because there is no doubting the value of constraints in maintaining data integrity when you are perhaps accessing your data in different ways (ie. not just forms).

This is perhaps one to think about a bit more, because your decisions can have a big impact on the way your application works for its users. As far as I can see, relying on integrity constraints as the only form of validation puts you back to a sort of block mode of operation. And if you couple that with form level validation, you have duplicated validation routines.

Perhaps someone can correct any misconceptions I have here, or point to any ways around this.

-- 
Glenn Nicholas.
Received on Wed May 04 1994 - 13:39:26 CEST

Original text of this message