To enhance performance when using constraints, primary and foreign keys, the FASTFK setting was added in R:BASE. When using FASTFK, R:BASE does not automatically build an index for foreign keys although indexes are still automatically built for primary keys. With FASTFK, instead of an index, R:BASE simply keeps a counter in file 3, the index file, for the foreign key column. Each foreign key value has a counter keeping track of how many records have this value. This maintains the referential integrity of foreign keys, but does not adversely affect performance by keeping and maintaining an index with an address pointer.
Constraints ensure referential integrity in databases without doing any programming. An index is automatically built and maintained for each primary and foreign defined for a table. Many times, however, foreign keys are used simply to verify that values exist in lookup tables. In a database following good relational design, a table can have many foreign keys, each one of which is indexed. Columns that normally would not be indexed, are now indexed because they are defined as foreign keys. This affects performance when adding new rows of data, deleting rows or updating data. All the indexes in a table are automatically updated whenever a new row of data is added, or a row is deleted. With updates, when the indexed column changes, its index is updated.
For foreign keys defined to verify lookup values, the lookup table has the corresponding column defined as a primary key which is indexed. That is the index that is used when the form, report, or program does the foreign key lookup. The primary key index is used to verify the data, not the foreign key. Turning the foreign key index off does not affect the lookup speed but dramatically improves the maintenance speed.
For columns that are defined as foreign keys where you need to maintain indexes, simply define a regular, single-column index in addition to the foreign key definition or use the FOREIGN INDEX option of the ALTER TABLE command. CREATE INDEX can be used at any time after a foreign key has been defined when FASTFK is ON. FOREIGN INDEX creates the foreign key and the index at the same time. It cannot be used when FASTFK is ON if the foreign key is already created. FOREIGN INDEX results in a smaller file 3 as only one entry is maintained for the column. When FASTFK is ON, if you define a separate index for a foreign key using CREATE INDEX, file 3 has an entry for the FASTFK counter, and an entry for the index.
To turn FASTFK ON, you must be in single-user mode. Set it to ON, then PACK KEYS to rebuild indexes. The FASTFK setting does not take effect until indexes are rebuilt. The same holds true if you want to turn FASTFK OFF and revert to maintaining true indexes on all foreign keys. After changing the setting of FASTFK, indexes must be rebuilt for the setting to take effect. Make sure the FASTFK setting is set correctly when doing any database maintenance such as PACK or RELOAD.
Two lines display when showing the FASTFK setting, the current setting, and the current setting for the database. The CVAL function returns the current setting for the open database. Below, the settings indicate that the next time indexes are globally rebuilt, FASTFK ON will be used, and foreign keys will not have indexes built for them.
R>SHOW FASTFK
(FASTFK ) OFF Use fast Foreign Key (FK) structures on rebuild
OFF FASTFK setting for current database
R:BASE automatically senses a database's FASTFK setting and connects a user in the correct FASTFK mode. FASTFK ON is stored in the version flag of file 1 of a database. A FASTFK database has a normal version flag of -811 when disconnected. To change the FASTFK setting, you must issue the SET FASTFK command after the database is connected. When a database is initially connected, both settings are always the same.
