Re: Constraints

Twaddell, Ron R. wrote: > > I have an interesting question. That's what they all say >;-)) > We are developing a systems using the Fourgen case tools, please do > not comment on this for it was not our decision to use Fourgen tools, > and would like to add database constrains for Referential Integrity. > > The problem arises with the tools when creating a screen with a parent > to child to grandchild relationship. Fourgen's insert logic inserts > the grandchild first, the parent second, and last the child. This > violates the constraint on the primary key of the grandchild. I think you mean a foreign key constraint on the grandchild. It references the primary key of its parent row. > To make a very long story a bit shorter, we are considering using the > "DEFER CONSTRAINTS" statement to allow the insert to happen within a > transaction. > So my question boils down to: > > 1) Has any body tried this? Raise one hand, class.. > 2) When the transaction is committed and the constraints are > re-established, does the engine check the whole table for a constraint > violation or just the information added or updated during the > transaction which "DEFER CONSTARINTS"? In a nutcase.. i mean nutchell: When operating under "deferred constraints", the engine notes each constraint violation as it occurs and plunks it on a stack. When you commit, the engine starts to march down that violations stack. For each violation it finds on the stack it asks: "Is this violation still violated?" If you have doen your job correctly, the answer will always be NO and it will continue down the stack until it is empty. Only then will the engine commit your transaction. If the engine finds ANY violation still in effect, it will roll back the whole blessed transaction. How you find what row caused the violation is *your* problem. See? Even a great option like deferring constraints has a down side. :-( > 3) Is there a better way which does not require major changes to > FOURGEN code? When I did it, I never had to do any major changes. I used an extention file. Here's a snippet of the code: ======================================================================= start file "detail.4gl" before block lld_delete delete insert if menu_item = "update" # If this delete is merely here then # to update the set of zone details call defer_constraints() end if ; ======================================================================= In my case, I was updating a detail row. However, each detail row has detail rows of its own in a third table. The logic to update detail rows [that are represented by a screen array] is to delete them all from the database and reinsert them from the corresponding program array. Of course, in my case, this delete part represented a violation of a foreign key constraint, referencing the third table. BTW, the reason I had to call a defer_constraints() function is that the 6.x version of 4GL does not uderstand this statement - its syntax is still mired in the 4.1x SQL syntax. I believe this problem has gone away in 4GL-7.2. Also, the function is defined in a source file called custom.4gl. I don't recall if this is generated as a stub or if I had to write it from scratch. I know I did NOT have to break any generated code to do this. -- -- Jake (Retrospectively realizes there is no future in hindsight) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+