referential.integrity.b-tree Article/Article


Command referential.integrity.b-tree Article/Article
Applicable release versions: AP
Category Article (24)
Description discusses the bridge processing code.

Contributed by Chris Alvarez. Original article ran in PickWorld Magazine.

One common problem in the management of a database is the ability to provide referential integrity. The postrelational database model, upon which Pick is based, allows the developer to develop relationships between files, thus eliminating the need for duplicate data.

This relationship in the Pick world is known as the translation processing code. This is what provides the automatic join capability in Pick. Using this processing code, the order file needs only to hold the customer item-id in order to build dictionaries that translate and retrieve any piece of information on that customer's item.

While this relationship is one of the major reasons for choosing Pick, it can also lead to problems in the area of referential integrity when the customer item that is used in 200 orders needs to be changed to a different item-id because of duplicate customer item for the same customer. Hence the Bridge processing code was added to Advanced Pick to insure referential integrity.
Referential integrity has been defined in many ways, but basically it is the ability for the system to maintain these parent/child relationships automatically. The bridge processing code makes it possible for the system to automatically handle the above situation.

When the customer's item-id is changed to a new item-id, every order with a reference to the old number is automatically changed to the new number. For our first example, create a CUSTOMER and an ORDER file using the following commands at TCL:



The next step is to add the bridge processing code in the d-pointer of the file's data section. First, use the following command to add the processing code into the CUSTOMER file:


This command is a macro that uses the Update processor to edit the data section d-pointer. The following is an example of the screen:

DICT customer 'customer' size = 55

dictionary-code D
base 611207
modulo 1
attribute-type L
column-width 10

Use the return key and move the cursor down to the correlative attribute and add the following bridge processing code:


This processing code tells the system that each time an item is filed into the CUSTOMER file, use the order item-id in attribute 10 to add the customer item-id to attribute 1 of the ORDER file. This is an example of a single threaded bridge. The bridge may be made double threaded by adding the following line to the d-pointer for the data section of the ORDER file:


This processing code instructs the system to do just the opposite. Each time an item is filed into the ORDER file, use the item-id in attribute one to verify that the order item-id is in attribute 10 of the CUSTOMER file.

Bridges, just like b-tree indexes, are updated regardless of the method used to update the file. To see the processing code work, add a few customers to the CUSTOMER file. Next, enter an order into the ORDER file, using one of the customer item-ids on attribute 1.

Each time an order is filed, the order number will appear on attribute 10 of the customer's item. Try copying one of the used customer items to a new item-id. All of the items in the ORDER file that referenced that customer item- id will be changed to the new one.

This feature is also available from Pick/BASIC with one simple statement. The "replace" statement replaces all occurrences of one item-id with a new one. This comes in handy when writing software that removes duplicate items. Whenever duplicates are found in the CUSTOMER file, one item can be deleted from the file and the replace command can be used to change all of the references in the ORDER file to the new customer.

The bridge processing code can be used one step further to actually make updates to the database. The Updating Bridge processing code can be used to make inventory adjustments, keep running totals on chart of accounts, etc. Addition and subtraction can be performed on an attribute in another file using a value from the current item. To continue our example, create an INVENTORY file and place the following processing code on the d-pointer for the data section of the ORDER file:


This processing code will update the quantity available held in attrib ute 1 of the INVENTORY file. Each time an order is filed, the system will use the value held in attribute 2 of the ORDER file as the item-id in the INVENTORY file, the value held in attribute 3 of the ORDER file will be added to attribute 1 in the INVENTORY file.