Improving Referential Integrity in Alpha Five

by Dr. Peter Wayne

What is Referential Integrity, anyhow?

Referential integrity is a way to assure that the linking relations you establish between your tables will remain intact. For example, in a typical application there is a customer table and a bill table, linked one to many on the customer_id field. If a customer's customer_id is changed, then the link to the bills is broken, and the bills are "orphaned." Alternatively, if the customer is deleted, then the bills also become orphaned unless they also are deleted from the system. Referential integrity comes in 2 flavors:

1. Prevent changes to the linking field and prevent deletions of the parent record
2. Cascade changes to the linking field and cascade deletions of the parent record

For this article, we'll create 2 simple tables: customer.dbf and bills.dbf. The structure of the tables is:

Next, we'll establish a one-to-many link from customer to bills:

Figure 3. Create a one-to-many link from customer to bills.

Notice in the set creation dialog, we can decide what kind of referential integrity to enforce. Let's look at what happens with each form of referential integrity:

• Cascade changes/deletes: if we choose this, then every time we enter change mode in the parent table, customer.dbf, Alpha Five will lock all the child records for the same customer. That's the only way Alpha Five can guarantee that changes to the cust_id field will propagate down to the same field in the child table. If a customer record is deleted or marked, then the customer's bills will also be deleted or marked. More significantly, if you have individually marked any of the customer's bills, and then you make any change to the customer record in the parent table, then the marked child records will be automatically unmarked by Alpha Five.
• Prevent changes: if we choose this option, then no changes will be permitted to the cust_id field in customer, and deletions from customer will not be allowed.

Either form of referential integrity is only enforced when the tables are opened in the set. If the tables are opened individually, then referential integrity is not enforced. This is a serious loophole! If you have a form based solely on customer.dbf in which you update customer information, you cannot rely on referential integrity to keep your operators from deleting individual customers or changing the cust_id fields.

That's the bad news. The good news is that you can use Alpha Five's field rules and record-based events to overcome the limitations of Alpha Five's referential integrity.

Prevent Changes/Deletes

The easier rule to implement on our own is the one to prevent changes/deletes. We need to limit 3 activities:

1. deletions from the parent table
2. changes to cust_id in the parent table
3. changes to cust_id in the child table

We can prevent deletions from the parent table by simply creating a one-line script in the CanDeleteRecord event for the record. If you've never created a record-level script before, it's really quite easy. Under Field Rules for customer.dbf, choose the Events tab. You will see an Action Scripting editor window for the CanEditField event for the first field in the table:

Figure 4. The Event editor in the table's field rules.

If you now expand the drop-down list under Container, you can access the table name itself:

Figure 5. The table's name is in the drop-down Container list.

Once you select the appropriate container, the list of available events for which you can create scripts changes from field-level events (CanEditField, etc.) to record-level events:

Figure 6. The table is the container so the scripting events are record-level events.

We can prevent deletions in the parent table with the one-line script,
cancel()
in the CanDeleteRecord event. You can even provide a helpful message to the user to explain why the deletion has failed:

cancel()
ui_msg_box("Deletions not allowed","Referential Integrity is enforced!")

Script 1. Prevent Deletions in customer

If you want to get particularly fancy, you can allow deletions of customers without invoices but not of customers with invoices by modifying Script 1 slightly:

dim tbl as p
tbl=table.get("customer")
if exist(tbl.cust_id,"bills","cust_id") then
	cancel()
	ui_msg_box("Deletions not allowed","Referential Integrity is enforced!")
end if

Script 2. Allow deletions of customers without bills

Script 2 assumes that you have defined an index, cust_id, on the field of the same name in the child table, bills.

You can prevent any changes to the linking field in customer by placing a skip rule of .not. isblank("cust_id") in the Data Entry tab of the field rules for cust_id:

Figure 5. Some people can't find the Skip rule expression. It's at the bottom of the Data Entry tab - you may have to scroll down to locate it.

You should make a Skip expression of .T. for cust_id in bills, so that a bill cannot be reassigned from one customer to another.

Cascade Changes/Deletes

Now that we've taken care of the easier of the referential integrity rules, how would we implement the cascading changes/deletes option? We'll do it in 2 stages: Cascading changes and Cascading Deletions. To implement Cascading Changes, we must guarantee that any change to the cust_id field in customer is reflected in a change to the cust_id field of the appropriate child records in bills.

Our first step is to remove the skip rule for the cust_id fields in both customer and bills. After removing those rules, we have to think about the steps involved in a change to the cust_id field in customer:

1. Save the current cust_id value
2. Make the change
3. Find every matching record in bills and change the cust_id fields in those records

Step (1), saving the current cust_id value, is accomplished with an OnChangeRecord event script in customer:

dim shared old_cust as c
old_cust=table.get("customer").cust_id

Script 3. OnChangeRecord script for customer

This script places the current cust_id value in a variable,old_cust. Because old_cust is declared as a shared variable, it will not be lost at the end of the script, but will persist and can be retrieved in other scripts.

Step (2), making the change, is done automatically by Alpha Five. However, as Alpha Five saves the record, we have to determine whether Alpha Five is about to save a new customer record or a changed customer record. This information about the state or mode of the customer record is accessible in the CanSaveRecord event, which is activated just before Alpha Five saves the record.

Step (3), propagating the changes down to the child records in bills, can be done in either the CanSaveRecord or OnSaveRecord event of customer. Since we are already going to code for the CanSaveRecord event, we'll minimize the number of scripts we need by putting everything in one place. First, we'll check to see whether the SAVE is occurring after an ENTER or a CHANGE. If the save is occurring after a new record creation, or ENTER, then no further action is necessary, but if the save is occurring after a CHANGE, then we'll check to see whether the cust_id field was changed. If cust_id was changed, then we must find all the matching records in bills and change them as well. The Xbasic is straightforward:

CONSTANT CHANGE=1
dim tbl_mode as n
tbl_mode=table.get("customer").mode_get()
if tbl_mode=CHANGE then 
	dim shared old_cust as c
	new_cust=table.get("customer").cust_id
	if old_cust<>new_cust then
		bills=table.open("bills")
		query.filter="cust_id='"+old_cust+"'"
		query.options=""
		query.order=""
		qry=bills.query_create()
		bills.fetch_first()
		while .not. bills.fetch_eof()
			bills.change_begin()
				bills.cust_id=new_cust 
			bills.change_end()
			bills.fetch_next()
		end while 
		qry.drop()
		bills.close()
	end if
end if

Script 4. The CanSaveRecord script for customer: Changing the child records when the parent has been altered.

Note in Script 4 that I am careful to drop the query created in bills. Although not strictly necessary, I have had enough problems with persistent queries in Alpha Five to make a habit of explicitly dropping all queries as soon as I am through with them.

The steps needed for Cascading Deletions are very similar to those we just implemented in cascading changes. We can look at the cust_id value of the about-to-be-deleted customer in the CanDeleteRecord event. Here is the script:

old_cust=table.get("customer").cust_id
bills=table.open("bills")
bills.delete_range("cust_id='"+old_cust+"'")
bills.close()

Script 5. The CanDeleteRecord script for customer.

You can see that Script 5 is simpler than Script 4, because Alpha Five has given us the batch deletion method, delete_range().

Efficiency considerations

So far, we've only delved into security considerations. We've shown how using our own event-level field rules we can enforce referential integrity even when tables are opened outside of the set in which referential integrity is defined. But from an efficiency standpoint, referential integrity is like driving uphill with the hand brake engaged. In my tests of referential integrity on sample tables, I have found that referential integrity slows down Alpha Five by a factor of 13! That's because when Alpha Five uses its own referential integrity, it locks all child records every time a user begins to make a change in a parent record, just in case it has to cascade changes or deletes. This locking is time-consuming and it occurs even if you have a skip rule in the the parent table to prevent a change to the linking field: Alpha Five's referential integrity isn't smart enough to know when the locking is unnecessary.

Multi-user considerations

Why would Alpha Five have such an inefficient implementation of referential integrity? I can promise you that Alpha Five's developers know a lot more about programming than I do! Both Script 4 and Script 5 above can fail in a multiuser environment, specifically because another user may be changing records in bills at the same time that these scripts are trying to modify them. By waiting for a lock on all the child records every time a change is attempted to the parent record, Alpha Five solves the problem of multiple users: no change can be initiated in a table with referential integrity enforced until all possible relevant records are safely locked.

We can modify our scripts to recover gracefully from a multiuser conflict. Alpha Five doesn't provide us with a way to lock individual records, but it does give us the batch_begin() and batch_end() methods. These methods prevent write access to a table by other users. We can use them like this in Script 4:

CONSTANT CHANGE=1
dim tbl_mode as n
tbl_mode=table.get("customer").mode_get()
if tbl_mode=CHANGE then 
	dim shared old_cust as c
	new_cust=table.get("customer").cust_id
	if old_cust<>new_cust then
		bills=table.open("bills")
		query.filter="cust_id='"+old_cust+"'"
		query.options=""
		query.order=""
		qry=bills.query_create()
		bills.fetch_first()
		on error goto cant_lock
		bills.batch_begin()
		on error goto 0
		while .not. bills.fetch_eof()
			bills.change_begin()
				bills.cust_id=new_cust 
			bills.change_end()
			bills.fetch_next()
		end while
		bills.batch_end() 
		qry.drop()
		bills.close()
	end if
end if
end

cant_lock:
on error goto 0
cancel()
ui_msg_box("Multiuser conflict","Change to cust_id not saved")
end

Script 6. This CanSaveRecord script tries to initiate a batch change to the child table. If it can't initiate a batch change, then an error occurs, and the script issues a cancel() and the change to the parent record does not occur.

We can make an analogous change to the CanDeleteRecord event to protect against multiuser conflicts. Since the delete_range() method is already a batch method, we don't need to use separate batch_begin() and batch_end() commands:

old_cust=table.get("customer").cust_id
bills=table.open("bills")
on error goto cant_lock
bills.delete_range("cust_id='"+old_cust+"'")
bills.close()
end

cant_lock:
on error goto 0
cancel()
bills.close()
ui_msg_box("Multiuser conflict","Could not delete customer - try later")
end

Script 7. Modification of CanDeleteRecord script to take into account possible multiuser conflicts.

If you find that you have frequent multiuser conflicts, you can put in some code to wait a second and try the lock again. In most applications that shouldn't be necessary, since any change to the linking or key field should be rare. But for completeness, I'll sketch out a revision of Script 7 that will try 3 times to cascade the deletion before giving up:

dim tries as n
tries=0
old_cust=table.get("customer").cust_id
bills=table.open("bills")
retry:
on error goto cant_lock
bills.delete_range("cust_id='"+old_cust+"'")
bills.close()
end

cant_lock:
on error goto 0
tries=tries+1
if tries<4 then
	sleep(1)   ' pause a second
	resume retry
else
	cancel()
	bills.close()
	ui_msg_box("Multiuser conflict","Could not delete customer - try later")
end if
end

Script 8. A fully multi-user aware CanDeleteRecord script.

last revised 1/18/99 - pkw

Don't forget, we need your feedback to make this site better!

Return to home