Foxhound is the better* Database Monitor for SQL Anywhere.
*better: More thorough, more relevant, more effective.
...more Alerts, more All Clears, more details, more control in your hands.
Last modified: May 17, 1996
mail to: email@example.com
Referential integrity is a wonderful thing but sometimes it gets in the way. Here's an example:
You have several related tables and you want to let the user add, change and delete rows in all these tables as part of one "business transaction". That means the user can choose to save or cancel all the changes. If "save" is chosen then all the tables are updated as one database transaction with a single commit (or rollback if there is an error).
The new tab folder control in PowerBuilder 5 makes it easy to include several DataWindows on one window and issue several update() calls when the user asks to save all the changes.
Now, let's say two of these tables are called parent and child and there is a foreign key relationship between them. The user inserts related rows in parent and child, and also deletes related rows in both tables, and then clicks on Save.
If your program calls dw_parent.update() before dw_child.update() the first call will fail. That's because dw_parent.update() issues all the SQL commands having to do with the parent table before your program has a chance to call dw_child.update(). It works for the SQL insert but not for the delete because that would create an orphan, a child row with no corresponding parent. Figure 1 shows the error message produced by SQL Anywhere 5.0.
SQLSTATE = 23000 [WATCOM][ODBC Driver]Integrity constraint violation: primary key for row in table 'parent' is referenced in another table No changes made to database. DELETE FROM "parent" WHERE "parent_key" = '1' AND "column1" = '1' AND "column1" = '1'
SQLSTATE = 23000 [WATCOM][ODBC Driver]Integrity constraint violation: no primary key value for foreign key 'fk_parent' in table 'child' No changes made to database. INSERT INTO "child" ( "parent_key", "child_key", "column1" ) VALUES ( '4', '1', '1' )It's tempting to change the foreign key relationship to on delete cascade to solve the problem in Figure 1 but that creates two new problems. First, many database designers avoid cascading deletes because they are quite dangerous: "I thought this parent had no children and now the whole family is gone!"
Second, if the dw_parent.update() call causes cascaded deletes the user must be very careful when deleting rows in dw_child. It's OK to delete a child row when its parent row is not being deleted, but if the call to dw_parent.update() has already caused a cascaded delete then dw_child.update() will fail because the row is already gone.
SQL Anywhere 5.0 offers a better solution in the wait_for_commit option. By default this is set to off to cause referential integrity checking as each SQL insert, update and delete command is executed. If you set it to on then all the checks are deferred until the SQL commit is issued.
Figure 3 shows how to change the wait_for_commit setting for the current connection to allow the separate dw_parent.update() and dw_child.update() calls to work.
string ls_sql ls_sql = "set temporary option wait_for_commit = on" execute immediate :ls_sql using SQLCA; if SQLCA.SQLCode <> 0 then MessageBox ( "Error", "Set option failed." ) return end ifThere is a Dark Side to wait_for_commit: If you actually do have a real referential integrity error the message produced by the failed commit won't be nearly as informative as the ones shown in Figures 1 and 2. In particular, the error message will not show the row in error.