Subject Re: [IBO] Foreign Key Constraint Exception Handling in Datagrid
Author Helen Borrie
At 04:04 AM 20/01/2004 +0000, you wrote:

> > If you silence an exception, it doesn't cause the error to be
>fixed by
> > magic. The idea is NOT to kill the exception but to *catch* it and
> > the user with the opportunity to fix it.
>It is my intention to catch the error, however I am not sure how to
>go about this.

Well, it's not different to normal Delphi exception handling. There's
quite a lot about this in the Delphi help and no doubt in any third-party
books you have.

There is a TI Sheet on the subject at

>FK Violations are conventionally unallowed, what I wish to allow the
>user to do is to update the FK if necessary to add a new part. I intend to
>have a cascading input so that a new part can be added and each FK
>Violation caught and allow the user to update the FK table so as to have no

>has occured to me, that most of the operators at the place I work
>have difficulty when adding a new item, because they must start with
>making sure all the new parts are added before the new sub-assemblies are
>and before the new item is added. This seems backwards for them, and
>with the complex item-subassembly-part relationship and the sheer number of
>parts per subassemblies and subassemblies per item, invariably the user
>some parts, causing a FK violation that he must sort out himself by
>adding the correct key. I intend to implement something simmilar to
>drop down boxes and offers to update the FK to erase the violation.

You won't be able to do this automatically within the confines of a
master-detail structure. How I handle this *very common* situation is to
have a completely separate dialog form for inserts that is invoked when the
user hits the New button.

The logic "cascades" upwards, taking advantage of Firebird's ability to
accept NULL as an interim foreign key. This means the "flow" can be made to
follow the user logic rather than be forced to follow "database logic".

The user creates a new detail record - or a bunch of them intended for the
same immediate master. As she creates it she has the option to pick an
immediate master from a dropdown, or just post the batch with null in the
FK. The BeforePost event checks whether the FK is null and, if so, pops up
another dialog to add a new immediate master. She either does that or
cancels that option, which takes her back to requiring to select from the
dd-list. Either way, that "step" in the cascade completes when there is
either a new immediate master (which itself has already cascade up to get
its FK) or a selected existing one. The detail record is then posted with
an actual value in the FK.

This cascading logic repeats upward as required. When the complete dialog
is finished (or cancelled) the dialog is closed and you refresh the main form.

It's hard to describe this workflow properly without pencil and paper and
maybe the abominable flow diagram. My fave CASE tool for this type of
design is the back of a paper tablecloth and a 3B pencil (or a roll of
handee towel or newsprint roll-ends :-))

One thing you *mustn't* have for this kind of flow is a NOT NULL constraint
on the foreign key fields in the hierarchy. And this is *definitely* one
set of requirements where you will use surrogate keys throughout -
invisible to the user, who gets the privilege of typing in the Human
Readable stuff, typos and all. If you've got things like stock item codes
as keys in the tables, now's the time to eschew that and abstract those
relationships into the atomic protection of generated keys.