Building your database includes creating tables. When you designed your database, you specified the table fields and relationships needed for your application. Now, as you create those tables, you make more detailed choices about the data types, captions, and potential default values for each field, the triggers for each table, as well as the table indexes you build to establish relationships between tables. This chapter describes the process of creating, refining, and relating tables and indexes as you develop an application. It focuses primarily on using the language to work with tables and records, but also explains using the interface to handle common tasks.
This chapter discusses:
You can create a table in a database, or just create a free table not associated with a database. If you put the table in a database, you can create long table and field names for database tables. You can also take advantage of data dictionary capabilities for database tables, long field names, default field values, field- and record-level rules, as well as triggers.
A Visual FoxPro table, or .dbf file, can exist in one of two states: either as a database table (a table associated with a database) or as a free table that is not associated with any database. Tables associated with a database have several benefits over free tables. When a table is a part of a database you can create:
Some features apply only to database tables. For information about associating tables with a database, see Chapter 6, Creating Databases.
Database tables have properties that free tables don’t.
You can design and create a table interactively with the Table Designer, accessible through the Project Manager or the File menu, or you can create a table programmatically with the language. This section primarily describes building a table programmatically. For information on using the Table Designer to build tables interactively, see Chapter 2, Creating Tables and Indexes, in the User’s Guide.
You use the following commands to create and edit a table programmatically:
Commands for Creating and Editing Tables
| ALTER TABLE | CLOSE TABLES |
| CREATE TABLE | DELETE FILE |
| REMOVE TABLE | RENAME TABLE |
| DROP TABLE |
You can create a new table in a database through the menu system, the Project Manager, or through the language. As you create the table, you can create long table and field names, default field values, field- and record-level rules, as well as triggers.
To create a new database table
-or-
For example, the following code creates the table smalltbl with one column, called name:
OPEN DATABASE Sales
CREATE TABLE smalltbl (name c(50))
The new table is automatically associated with the database that is open at the time you create it. This association is defined by a backlink stored in the table’s header record.
A free table is a table that is not associated with a database. You might want to create a free table, for example, to store lookup information that many databases share.
To create a new free table
-or-
For example, the following code creates the free table smalltbl with one column, called name:
CLOSE DATABASES
CREATE TABLE smalltbl FREE (name c(50))
If no database is open at the time you create the table, you do not need to use the keyword FREE.
When you issue the CREATE TABLE command, you specify the file name for the .dbf file Visual FoxPro creates to store your new table. The file name is the default table name for both database and free tables. Table names can consist of letters, digits, or underscores and must begin with a letter or underscore.
If your table is in a database, you can also specify a long table name. Long table names can contain up to 128 characters and can be used in place of short file names to identify the table in the database. Visual FoxPro displays long table names, if you’ve defined them, whenever the table appears in the interface, such as in the Project Manager, the Database Designer, the Query Designer, and the View Designer, as well as in the title bar of a Browse window.
To give a database table a long name
-or-
For example, the following code creates the table vendintl and gives the table a more understandable long name of vendors_international:
CREATE TABLE vendintl NAME vendors_international (company C(40))
You can also use the Table Designer to rename tables or add long names to tables that were created without long names. For example, when you add a free table to a database, you can use the Table Designer to add a long table name. Long names can contain letters, digits, or underscores, and must begin with a letter or underscore. You can’t use spaces in long table names.
You can rename database tables through the interface because you are changing the long name. If you remove the table from the database, the file name for the table retains the original name. Free tables do not have a long name and can only be renamed using the language.
To rename a table in a database
To rename a free table
Caution If you use the RENAME command on tables associated with a database, the command does not update the backlink to the database and can cause table access errors.
If a table is associated with a database, you can delete the table as a by-product of removing the table from its database. Deleting a table is different from removing a table from a database, however. If you just want to remove a table from a database but do not want to physically delete the table from disk, see “Removing a Table from a Database” in Chapter 6, Creating Databases.
To delete a database table from disk
-or-
-or-
-or-
Caution If you use the ERASE command on tables associated with a database, the command does not update the backlink to the database and can cause table access errors.
The following code opens the database testdata and deletes the table orditems and its indexes, default values, and validation rules:
OPEN DATABASE testdata
DROP TABLE orditems
If you delete a table using the DELETE clause of the REMOVE TABLE command, you also remove the associated .fpt memo file and .cdx structural index file.
If a table is not associated with a database, you can delete the table file through the Project Manager or with the DELETE FILE command.
To delete a free table
-or-
For example, if sample is the current table, the following code closes the table and deletes the file from disk:
USE
DELETE FILE sample.dbf
The file you want to delete cannot be open when DELETE FILE is issued. If you delete a table that has other associated files, such as a memo file (.fpt) or index files (.cdx or .idx), be sure to delete those files as well. For example, if the file sample.dbf also has an associated memo file, you could delete both files with the following commands:
USE
DELETE FILE sample.dbf
DELETE FILE sample.fpt
You can make a copy of a tables structure, its stored procedures, trigger expressions, and default field values by using the language. There is no menu option to perform the same function. This procedure does not copy the contents of the table.
To duplicate a table
To modify the structure of an existing table, you can use the Table Designer or ALTER TABLE. Alternatively, you can create a new table based on the structure of an existing table, then modify the structure of the new table.
To copy and edit a table structure
The new table is empty.
You can use the Save As HTML option on the File menu when you are browsing a table to save the contents of a table as an HTML (Hypertext Markup Language) file.
To save a table as HTML
When you create table fields, you determine how data is identified and stored in the table by specifying a field name, a data type, and a field width. You also can control what data is allowed into the field by specifying whether the field allows null values, has a default value, or must meet validation rules. Setting the display properties, you can specify the type of form control created when the field is added onto a form, the format for the contents of the fields, or the caption that labels the content of field.
Note Tables in Visual FoxPro can contain up to 255 fields. If one or more fields can contain null values, the maximum number of fields the table can contain is reduced by one, from 255 to 254.
You specify field names as you build a new table. These field names can be 10 characters long for free tables or 128 characters long for database tables. If you remove a table from a database, the table’s long field names are truncated to 10 characters.
To name a table field
-or-
For example, to create and open the table customer with three fields, cust_id, company, and contact, you could issue the following command:
CREATE TABLE customer (cust_id C(6), company C(40), contact C(30))
In the previous example, the C(6) signifies a field with Character data and a field width of 6. Choosing data types for your table fields is discussed later in this section.
Using the ALTER TABLE command, you add fields, company, and contact to an existing table customer:
ALTER TABLE customer ;
ADD COLUMN (company C(40), contact C(30))
When you create a table in a database, Visual FoxPro stores the long name for the table’s fields in a record of the .dbc file. The first 10 characters of the long name are also stored in the .dbf file as the field name.
If the first 10 characters of the long field name are not unique to the table, Visual FoxPro generates a name that is the first n characters of the long name with the sequential number value appended to the end so that the field name is 10 characters. For example, these long field names are converted to the following 10-character names:
| Long Name | Short Name |
| customer_contact_name | customer_c |
| customer_contact_address | customer_2 |
| customer_contact_city | customer_3 |
| ... | ... |
| customer_contact_fax | customer11 |
While a table is associated with a database, you must use the long field names to refer to table fields. It is not possible to use the 10-character field names to refer to fields of a table in a database. If you remove a table from its database, the long names for the fields are lost and you must use the 10-character field names (stored in the .dbf) as the field names.
You can use long field names composed of characters, not numbers, in your index files. However, if you create an index using long field names and then remove the referenced table from the database, your index will not work. In this case, you can either shorten the names in the index and then rebuild the index; or delete the index and re-create it, using short field names. For information on deleting an index, see “Deleting an Index” later in this chapter.
The rules for creating long field names are the same as those for creating any Visual FoxPro identifier, except that the names can contain up to 128 characters.
For more information about naming Visual FoxPro identifiers, see Creating Visual FoxPro Names.
As you create each table field you also choose a data type for the data the field is to store. When you choose a field’s data type, you’re deciding:
Tip For phone numbers, part numbers, and other numbers you don’t intend to use for mathematical calculations, you should select the Character data type, not the Numeric data type.
To choose a data type for a field
-or-
For example, to create and open the table products with three fields, prod_id, prod_name, and unit_price, you could issue the following command:
CREATE TABLE products (prod_id C(6), prod_name C(40), unit_price Y)
In the previous example, the ‘Y’ after the unit_price field name specifies a Currency data type.
For more information about specific data types, see Data and Field Types.
As you add a field, you can quickly define a regular index on the field by specifying ascending or descending in the Index column of the Table Designer. The index you create is automatically added to the Index tab and uses the field as the expression. To modify the index, you can switch to the Index tab to change the index name, type, or to add a filter.
As you build a new table, you can specify whether one or more table fields will accept null values. When you use a null value, you are documenting the fact that information that would normally be stored in a field or record is not currently available. For example, an employee’s health benefits or tax status may be undetermined at the time a record is populated. Rather than storing a zero or a blank, which could be interpreted to have meaning, you could store a null value in the field until the information becomes available.
To control entering null values per field
When the Null column is selected, you can enter null values in the field.
-or-
For example, the following command creates and opens a table that does not permit null values for the cust_id and company fields but does permit null values in the contact field:
CREATE TABLE customer (cust_id C(6) NOT NULL, ;
company C(40) NOT NULL, contact C(30) NULL)
You can also control whether null values are permitted in table fields by using the SET NULL ON command.
To permit null values in all table fields
-or-
When you issue the SET NULL ON command, Visual FoxPro automatically checks the NULL column for each table field as you add fields in the Table Designer. If you issue the SET NULL command before issuing CREATE TABLE, you don’t have to specify the NULL or NOT NULL clauses. For example, the following code creates a table that allows nulls in every table field:
SET NULL ON
CREATE TABLE test (field1 C(6), field2 C(40), field3 Y)
The presence of null values affects the behavior of tables and indexes. For example, if you use APPEND FROM or INSERT INTO to copy records from a table containing null values to a table that does not permit null values, then appended fields that contained null values would be treated as blank, empty, or zero in the current table.
For more information about how null values interact with Visual FoxPro commands, see Handling Null Values.
After you create a table in an open database, you can add a description of each table field to make your tables easier to understand and update. Visual FoxPro displays a field’s comment text in the Project Manager when you select the field in the list of fields for the table.
To add a comment to a field in a database table
-or-
For example, you might want to clarify what is stored in the unit_price field in your orditems table by entering “Current retail price per unit” as comment text for the field:
?DBSETPROP('orditems.price', 'field', 'comment', ;
'Current retail price per unit’)
For more information on using DBSETPROP( ) to set properties on database table fields, see Chapter 6, Creating Databases.
If you want Visual FoxPro to fill the contents of a field automatically as you add a new record, you can create a default value for the field. The default value is applied whether you enter data through a form, in a Browse window, a view, or programmatically, and remains in the field until you enter a new value.
You create default values either through the Table Designer or through the language. You can specify default values for any data type except General.
To assign a default value to a database table field
-or-
For example, you might want your application to limit the amount of merchandise a new customer can order until you’ve had time to complete a credit check and determine the amount of credit you’re willing to extend to that customer. The following example creates a maxordamt field with a default value of 1000:
CREATE TABLE customer (cust_id C(6), company C(40), contact C(30), ;
maxordamt Y(4) DEFAULT 1000)
If your customer table already included the maxordamt column, you could add a default value for the column with this command:
ALTER TABLE customer ALTER COLUMN maxordamt SET DEFAULT 1000
You can use default values to speed data entry for your application’s users, enabling them to skip a field unless they want to enter a different value. For example, if your business primarily deals with domestic customers, you may want the country field in the customer table in a database to be filled with the name of your country automatically. If you’re entering a customer record for an international customer, you can then overwrite the name of your country with their country name.
Tip If one of your application’s business rules requires that a field contain an entry, providing a default value helps to ensure that a particular field-level or record-level rule will not be violated.
If you remove or delete a table from a database, all default values bound to that table are deleted from the database. Stored procedures referenced by the removed or deleted default value remain even after the default value has been removed.
When you don’t specify a default value, a blank value (as defined for each data type) is inserted unless SET NULL is on. This preserves backward compatibility with any existing FoxPro code you might have.
You can use .NULL. as a default value if you want the field to use null values. Whether SET NULL is on or off, if you use .NULL. as a default value, Visual FoxPro inserts .NULL. for all commands except APPEND BLANK.
You can specify default values that are either scalar values (such as “a number”) or expressions that evaluate to a scalar quantity. You can also specify any valid Xbase expression that returns a value consistent with the data type for the field.
Visual FoxPro evaluates expressions for data type when the table structure is closed. If the data type doesn’t match the associated field type, Visual FoxPro generates an error. If the expression is a user-defined function (UDF) or contains a UDF, it is not evaluated.
When you create the default value through the language, the CREATE TABLE or ALTER TABLE commands will generate an error if the data types do not match. If the expression is a UDF or contains a UDF, it is not evaluated at CREATE time and no error is returned.
Default values are evaluated (if necessary) and placed in the appropriate fields when the APPEND, APPEND BLANK, or INSERT commands are issued.
When you assign values with the APPEND FROM or INSERT - SQL commands, Visual FoxPro assigns default values to any fields not explicitly assigned. The APPEND FROM and INSERT - SQL commands also respect default values. However, when either of these commands is issued, defaults will not overwrite existing values in fields. If appended or inserted fields contain values, the existing value is retained as the record is appended or inserted and the default value is not applied.
Default values are particularly useful to automatically populate fields that do not allow null values. When you add a new record, default values are applied first, then each field is checked in definition order for missing information. This ensures fields designated as NOT NULL have the opportunity to be populated with default values before the NOT NULL constraint is applied.
By specifying an input mask, you define the punctuation, spacing, and other format attributes of values as they are entered into the field. The values are then stored in a uniform manner which can reduce data entry errors and make processing more efficient. For example, adding a mask to a numeric field storing telephone numbers helps the user to quickly fill out the field because the punctuation and spaces are already provided by the mask.
To provide an input mask
-or-
For example, the following code specifies an input mask for a date:
DBSetProp("orders.postalcode","field","InputMask", "99999-9999")
Additional properties for fields allow you to control how a field and its values appear on forms, Browse windows, and reports. You can specify a display format, a default field caption, and a default class and class library.
A format provides an output mask that determines the manner in which the value of a field is displayed in a form, Browse window, or report. For example:
To provide a format
-or-
For example, the following code specifies a display format for a postal code:
DBSetProp("orders.postalcode","field","Format","@R 99999-9999")
You can create a caption for each field in a database table. Visual FoxPro displays a field’s caption text as the column header in a Browse window and as the default header name in a form grid.
To add a caption to a field in a database table
-or-
For example, you might want to create a caption for the fax field in your supplier table by entering “Supplier_Fax” as the caption for the field:
?DBSETPROP('supplier.fax', 'field', 'caption', 'Supplier_Fax')
For more information on using DBSETPROP( ) to set properties on database table fields, see Chapter 6, Creating Databases.
To save time later when you're creating forms, you can set a default class for a field. Once set, each time you add the field to a form, the control on the form uses the class you specify as the default. For example, character fields automatically appear as text box controls when you add them to a form. If you want to automatically create a combo box control instead when you use the field in a form, you can set that class as the default for this field. You can also use class libraries that you've created.
To set a default class
If you find you're often changing the library and class for your fields, you can map the fields data types to a library and class in the Options dialog box. For more information about mapping your field data types to classes, see Chapter 3, Configuring Visual FoxPro, in the Installation Guide. For more information about creating classes, see Chapter 3, Object-Oriented Programming, in this book.
You can enforce business rules for data entry by creating field-level and record-level rules, called validation rules, to control the data entered into database table fields and records. Field- and record-level rules compare the values entered against the rule expressions that you define. If the entered value does not meet the requirements of the rule, the value is rejected. Validation rules exist only in database tables.
Field- and record-level rules enable you to control the types of information entered into a table, whether the data is accessed through a Browse window, a form, or programmatically through the language. They allow you to consistently enforce the rule for a field with less coding than if you wrote the rule expression as code in a VALID clause on a form, or in a portion of program code. In addition, the rules you establish in a database are enforced for all users of the table, regardless of the requirements of the application.
You can also create candidate or primary indexes that prevent duplicate entries in a field, and triggers to enforce referential integrity or perform other actions when the data in your database is changed.
You choose database constraints based on the level at which you want to enforce a business or referential integrity rule, as well as the action that causes the constraint to be activated. The following table lists the data validation constraints in the order in which they are enforced by the Visual FoxPro engine, the level at which they are applied, and when the engine activates the validation.
| Enforcement Mechanism | Level | Activated |
| NULL validation | Field or column | When you move out of the field/column in a browse, or when the field value changes during an INSERT or REPLACE. |
| Field-level rules | Field or column | When you move out of the field/column in a browse, or when the field value changes during an INSERT or REPLACE. |
| Record-level rules | Record | When the record update occurs. |
| Candidate/primary index | Record | When the record update occurs. |
| VALID clause | Form | When you move off the record. |
| Triggers | Table | When table values change during an INSERT, UPDATE, or DELETE event. |
Constraints are activated in the order in which they appear in the table. The first violation of any validation test stops the command.
Candidate and primary indexes are explained later in this chapter in the section “Controlling Duplicate Values.”
When you want to control the type of information a user can enter into a field, and you can validate the data in a field independently of any other entry in the record, you use a field-level validation rule. For example, you might use a field-level validation rule to ensure that the user doesn’t enter a negative number in a field that should contain only positive values. You can also use a field-level rule to compare the values entered in a field against the values in another table.
You should not create field- or record-level rules that are application-specific. Use field- and record-level validation rules to enforce data integrity and business rules that always apply to the data in your database, regardless of who may access the data. For example, you might create a rule that compares the entry in the postal_code field of a table against a lookup table that contains the postal abbreviation codes for your country, and rejects any value that is not already present as a valid postal code abbreviation.
To create a field-level rule
-or-
-or-
For example, the following code adds a field-level validation rule to the orditems table requiring that numbers entered into the quantity field be 1 or greater:
ALTER TABLE orditems
ALTER COLUMN quantity SET CHECK quantity >= 1
When the user attempts to enter a value less than 1, Visual FoxPro displays an error and the value is rejected.
You can customize the message displayed when the rule is violated by adding validation text to the field. The text you enter is displayed instead of the default error message.
To add a custom error message to a field-level rule
-or-
For example, the following code adds both a field-level validation rule for the orditems table requiring that numbers entered into the quantity column must be 1 or greater, as well as a custom error message:
ALTER TABLE orditems ;
ALTER COLUMN quantity SET CHECK quantity >= 1 ;
ERROR "Quantities must be greater than or equal to 1"
When the user attempts to enter a value less than 1, Visual FoxPro displays an error with the custom error message you defined, and rejects the failed value. You can also use the SET CHECK clause of the ALTER TABLE command with the optional ERROR clause to create a custom error message.
Knowing When Field-Level Rules are Checked
Field-level rules are checked when the field’s value changes. Unlike triggers, field-level rules fire even if data is buffered. When you work with data in a Browse window, form, or other window, Visual FoxPro checks field-level rules as you move away from the field. If a field value has not been changed, the rule is not checked. This means that you are free to tab through fields without the system validating any of the data.
Field-level Rule Checking
| Data entry method | Window or command | Field-level rule checked |
| User interface | Browse window Form Other window |
As you move away from the field, if the field value has changed. (If the field value has not been changed, the rule is not checked.) |
| Commands that do not specify fields | APPEND APPEND GENERAL APPEND MEMO BROWSE CHANGE DELETE EDIT GATHER |
As field value changes, in field definition order. |
| APPEND BLANK INSERT INSERT - SQL |
As the record is appended or inserted. | |
| Commands that specify fields | UPDATE UPDATE - SQL REPLACE |
In the order in which fields are specified in the command. |
You use record-level validation rules to control the type of information a user can enter into a record. Record-level validation rules typically compare the values of two or more fields in the same record to make sure they follow the business rules established for the database. For example, you can use a record-level validation rule to ensure that one field’s value is always greater than that of another in the same record.
To create a record-level validation rule and custom error message
-or-
For example, you might want to ensure employees are 18 years or older when hired. The following code adds a record-level validation rule and error text for the employee table requiring that the date of hire entered into the hire_date column is greater than or equal to their birth date plus 18 years:
ALTER TABLE employee SET CHECK ;
hire_date >= birth_date + (18 * 365.25) ;
ERROR "Employees must be 18 years or older by date of hire"
If the user enters an employee record with an invalid date, Visual FoxPro displays an error with the custom error message you defined, and does not update the record.
You can also use the SET CHECK clause of the ALTER TABLE command to create a record-level validation rule. You should ensure that any rules specified for fields do not conflict semantically with the rules you define for the table. Visual FoxPro makes no attempt to compare the field-level and record-level expressions for consistency.
Knowing When Record-Level Rules are Checked
Record-level rules, like field-level rules, activate when the record value changes. No matter how you work with data, whether in a Browse window, form, or other user interface window, or through commands that alter data, Visual FoxPro checks record-level rules as you move the record pointer off the record. If no values within the record have changed, the record-level rule is not checked when you move the record pointer. You are free to move through records without the system validating any of the data.
If you modify a record, but don't move the record pointer, and then close the Browse window, the rule is still checked. You're warned of any errors that occur, and the Browse window is closed.
Caution Do not include any commands or functions in your validation rules that attempt to move the record pointer in the current work area (that is, in the work area whose rules are being checked). Including commands or functions such as SEEK, LOCATE, SKIP, APPEND, APPEND BLANK, INSERT, or AVERAGE, COUNT, BROWSE, and REPLACE FOR in validation rules may cause them to trigger recursively, creating an error condition.
Unlike triggers, record-level rules fire even if data is buffered. When a record-level rule fires during a running application, you need to include error handling code. Typically, this will mean not allowing the application to leave the form (or change the active environment, to be more generic) until the user either corrects the reported error or cancels the update.
If you remove or delete a table from a database, all field-level and record-level rules bound to that table are deleted from the database. This is because the rules are stored in the .dbc file, and removing a table from the database breaks the link between the .dbf file and its .dbc file. However, stored procedures referenced by the removed or deleted rule are not deleted. They are not automatically removed, because they may be used by rules in other tables that remain in the database.
A trigger is an expression that is bound to a table and is invoked when any of the table’s records are modified by one of the specified data-modification commands. Triggers can be used to perform any side-effect operations that a database application requires when data is modified. For example, you can use triggers to:
Triggers are created and stored as properties on a specific table. If you remove a table from a database, the triggers associated with that table are deleted. Triggers fire after all other checks, such as validation rules, primary key enforcement, and null value enforcement, are performed. And unlike field- and record-level validation rules, triggers don’t fire on buffered data.
You create triggers using the Table Designer or the CREATE TRIGGER command. For each table, you can create one trigger for each of the three events: INSERT, UPDATE, and DELETE. A table can have a maximum of three triggers at any one time. A trigger must return a true (.T.) or false (.F.) value.
To create a trigger
-or-
For example, perhaps each time Tasmanian Traders sells an item, they want to compare the remaining Units_in_stock against the Reorder_level and be notified if they need to reorder that item. You can create an Update trigger on the products table to accomplish this. Every time a product is sold, the Update trigger will fire and the Units_in_stock field will be updated to reflect the remaining items in stock.
To create the trigger, you can specify updProductsTrigger( ) as your Update trigger for the products table. You can add a field to products, named reorder_amount, which stores the amount you want to order each time you reorder the item, and create a reorder table with the fields: product_id and reorder_amount. You can then add this code to your stored procedure:
PROCEDURE updProductsTrigger
IF (units_in_stock+units_on_order) <= reorder_level
INSERT INTO Reorder VALUES(Products.product_id, ;
Products.reorder_amount)
ENDIF
ENDPROC
You can create similar triggers for an insert or delete event by using the FOR INSERT or FOR DELETE clause, respectively, instead of the FOR UPDATE clause. If you attempt to create a trigger that already exists for a particular event and table while SET SAFETY is on, Visual FoxPro asks you if you want to overwrite the existing trigger.
You can remove a trigger from a database table through the interface or with the DELETE TRIGGER command.
To delete a trigger
-or-
The following example removes the update trigger for the customer table:
DELETE TRIGGER ON customer FOR UPDATE
If you remove or delete a table from a database, all triggers bound to that table are deleted from the database. However, stored procedures referenced by the removed or deleted trigger are not deleted.
You can modify triggers through the Table Designer or through the language.
To modify a trigger
-or-
When you modify a trigger by first issuing the SET SAFETY OFF command and then re-creating the trigger, the old trigger expression is automatically deleted and replaced by the re-created trigger expression.
Visual FoxPro provides a Referential Integrity Builder to generate triggers and stored procedures that will enforce Referential Integrity (RI) for your database. For more information on using the RI Builder, see Chapter 6, Creating Databases.
After you’ve built a table you can always modify the table structure and properties. You may want to add, change or delete field names, widths, data types, change default values or rules, or add comments or captions.
You can open the Table Designer to modify your table’s structure, or you can make changes programmatically using the ALTER TABLE command. Make sure you have exclusive access to the table before modifying its structure.
To modify the structure of a table with the Table Designer
-or-
-or-
For example, you can modify the structure of the database table employee with the following commands:
OPEN DATABASE testdata
USE employee EXCLUSIVE
MODIFY STRUCTURE
Each of the previous options opens the Table Designer.
To modify the structure of a table programmatically
The ALTER TABLE command offers extensive clauses that enable you to add or drop table fields, create or drop primary or unique keys or foreign key tags, and rename existing fields. Some clauses apply only to tables associated with a database. A few specific examples are included in this section.
You can add a new field to a table with the Table Designer or with the language.
To add a field to a table
-or-
For example, the following command adds a field called fax to the customer table and allows the field to have null values:
ALTER TABLE customer ADD COLUMN fax c(20) NULL
You can delete an existing field from a table with the Table Designer or with the language.
To delete a field from a table
-or-
For example, the following command drops the field called fax from the customer table:
ALTER TABLE customer DROP COLUMN fax
Removing a field from a table also removes the field’s default value setting, rule definitions, and caption. If index key or trigger expressions reference the field, the expressions become invalid when the field is removed. The invalid index key or trigger expression will not generate an error until run time.
You can rename existing table fields in two ways.
To rename a table field
-or-
For example, to rename the column company in the customer table, you could issue the following command:
ALTER TABLE customer RENAME COLUMN company TO company_long_new_name
In the previous example, the new field name takes advantage of the ability to create long field names in database tables.
You can set new field-level or table rule expressions and rule text, as well as alter rules and text you established with CREATE TABLE or ALTER TABLE commands.
To change an existing rule
-or-
To view the current rule expression and associated text, you can use the DBGETPROP( ) function; these values are read-only for tables and can only be changed using the ALTER TABLE command.
You can set or change default values for table fields after you’ve built your table.
To change an existing default value
-or-
To view the current default value for a field, use the DBGETPROP( ) function; these values are read-only for tables and can only be changed using the ALTER TABLE command.
Once you’ve designed and created the structure for a table, you can store data in the table by adding new records. Later, you’ll change and delete existing records. Each of these tasks can be accomplished either through the interface or by using commands. This section focuses primarily on working with records programmatically. For more information about working with records through the interface, see Chapter 2, Creating Tables and Indexes, in the User’s Guide.
When you first create a Visual FoxPro table, it is open but empty. If you try to store data to a table without first creating a record in the table, nothing happens. The first step in adding records to a new table is to add rows to store the new data.
To add records to a table
The INSERT - SQL command can be used to insert values specified with the command or to insert values from an array or variable. For example, to insert a new record into the TasTrade database customer table, you could issue the following command:
INSERT INTO customer (cust_id, company, contact) ;
VALUES ("SMI007", "Smith's Delicatessen", "Sarah Smith")
The INSERT - SQL command is useful with remote data, as it uses ANSI-compliant SQL syntax.
You can also use the APPEND BLANK command followed by the REPLACE command to add a blank record to a table and then store data in a field. The APPEND BLANK appends a new, blank record to a table. The REPLACE command replaces the current value of a field, even an empty field, with a new value.
The REPLACE command requires:
The following example uses the APPEND BLANK command to create one record in which you can store data using the REPLACE command:
APPEND BLANK && record now available
REPLACE lastname WITH "SMITH" && store character value to the field
You can use the UPDATE - SQL command instead of the REPLACE command to update records in a table.
Another way to store data in records is to copy them from other tables or files. For example, you can append records from another table or file.
To append records from another file
-or-
Records can accept data directly, as in the previous example, where the INSERT command specified the text to be inserted into specific fields in the customer table, as well as from constants, variables, arrays, objects, and other data sources. For more information about other ways to import data, see Chapter 9, Importing and Exporting Data, in the User’s Guide.
If you want to add a new record while viewing a table in browse mode, you can choose Append Record from the Table menu. Conversely, if you want to prevent users from being able to append a new record while in browse mode, you can use the NOAPPEND clause of the BROWSE command.
You can enter data in a table interactively, through a Browse window, or programmatically, with the REPLACE or UPDATE - SQL commands. When you use the REPLACE or UPDATE - SQL in a multi-user application, you can turn on record or table buffering, which enables you to edit data without locking the record until you want to commit changes. For more information on record and table buffering, see Chapter 17, Programming for Shared Access.
You can display and edit existing records in a table through the interface or programmatically.
To display records for editing
For example, the following code displays the customer table in a Browse window in edit mode:
USE customer
EDIT
If you want to use a form to edit a record, create a text box in your form and set its DataSource property to the name of the table you want to edit. For more information about forms, see Chapter 9, Creating Forms.
You can also use the CHANGE and EDIT commands to make changes to specific fields in a table.
You can store graphics in a Visual FoxPro table by creating a General field and importing or pasting OLE objects, such as bitmaps or charts, into the field. The APPEND GENERAL command places an OLE object into a General field. The following example stores a Microsoft Excel chart file from the default Visual FoxPro directory into a General field named Chart:
APPEND GENERAL Chart FROM "CHART1.CLX" CLASS EXCELCHART
For more information about working with OLE objects in Visual FoxPro tables, see Chapter 16, Adding OLE.
You can enter a null value in a field through the language with the NULL token, or through the interface with a key combination if the field accepts null values.
To store a null value in a field
-or-
For example, the following code replaces the existing value in the field automobile with a null value:
REPLACE automobile WITH NULL
Note Use the SET NULLDISPLAY command to specify the text displayed for null values.
You delete records by marking them for deletion, then removing the deleted records. Until you remove the records that are flagged for deletion, they are still on disk and can be unmarked and restored. This section describes how to mark, unmark, and remove records from your table.
You can mark records for deletion through the interface or with the DELETE - SQL command.
To mark a record for deletion
-or-
-or-
You can use the DELETE - SQL command to specify a range of records, as well as a condition based on a logical expression that records must meet to be marked for deletion. For example, the following code marks for deletion all product records with ‘T’ in the Discontinu field:
USE products
DELETE FROM products WHERE discontinu = .T.
BROWSE
Records you mark for deletion are not physically removed from the table until you issue a PACK command. When you view the table in the Browse window, you’ll see that the deletion marker is flagged for each deleted record, but the record is still visible in the table, if SET DELETED is set to off. If SET DELETED is set to on, the records marked for deletion are excluded from the Browse window.
The setting of the SET DELETED command also affects whether records marked for deletion are accessible by commands that operate on records.
You can unmark records that were marked for deletion with the RECALL command. The RECALL command can recover records only if you have not issued a PACK or ZAP command, which physically deletes the records from the table.
To unmark a record marked for deletion
-or-
-or-
You can use the RECALL command to specify a range of records, as well as a condition based on a logical expression that records must meet to be unmarked for deletion. For example, the following code unmarks for deletion all product records with ‘T’ in the discontinu field:
USE products
RECALL FOR discontinu = .T.
BROWSE
When you view the table in the Browse window, you’ll see that the deletion marker is not flagged for the records.
After you’ve marked records for deletion, you can permanently remove them from disk through the interface or the language.
To remove records marked for deletion from disk
The PACK command has two clauses: MEMO and DBF. When you issue PACK without the MEMO or DBF clauses, records in both the table file and the associated memo file are removed. Make sure you have exclusive access to the table. For example, the following code removes records marked for deletion:
USE customer EXCLUSIVE
PACK
To delete records in the table file only and leave the memo file untouched, use PACK DBF.
Information in table memo fields is stored in an associated memo file with the same name as the table and an .fpt extension. If you want to remove unused space from the memo file without removing records that are marked for deletion, issue the PACK command with the MEMO clause. Make sure you have exclusive access to the table.
If you want to remove all records from a table, and leave just the table structure, you can use the ZAP command. Issuing ZAP is equivalent to issuing DELETE ALL followed by PACK, but ZAP is much faster. Make sure you have exclusive access to the table.
Caution Records zapped from the current table cannot be recalled.
When you want to navigate, view, or manipulate table records in a particular order, you use an index. Visual FoxPro uses indexes as ordering mechanisms to provide you with flexibility and power as you develop your application. You have the flexibility to create and use many different index keys for the same table, enabling you to work with records in different orders, according to your application’s requirements. You have the power to create custom relationships between tables based on their indexes, enabling you to access exactly the records you want.
A Visual FoxPro index is a file that contains pointers that are logically ordered by the values of an index key. The index file is separate from the table’s .dbf file, and does not change the physical order of the records in the table. Rather, when you create an index, you create a file that maintains pointers to the records in the .dbf file. When you want to work with table records in a particular order, you choose an index to control the order and increase the speed in which the table is viewed and accessed.
When you first create a table, Visual FoxPro creates the table’s .dbf file and, if your table includes Memo or General fields, the associated .fpt file. You can choose to quickly add an index to a field as you define the field; otherwise, no index files are generated at that time. Records you enter into the new table are stored in the order you entered them; when you browse the new table, they appear in the order they were entered.
Typically, you’ll want to be able to view and access the records in your new table in a specific order. For example, you may want to view the records in your customer table alphabetically by company name. When you want to control the order in which records are displayed and accessed, you create an index file for your table by creating the first ordering scenario, or index key, for your table. You can then set the order of the table to the new index key, and access the table’s records in the new order.
To create an index key for a table
-or-
For example, the following code uses the table customer and creates an index key on the city field. The keyword TAG and the word “city” afterward specifies a name, or tag, for the new index key on the city field.
USE customer
INDEX ON city TAG city
In the previous example, the tag for the index key uses the same name as the field you’re indexing. The names don’t have to match — you could also choose to give the index key a different name.
When you first create an index using the INDEX command, Visual FoxPro automatically uses the new index to set the order of the records in the table. For example, if you entered some data into the sample table created in the previous example, and then browsed the table, the records would appear in order by city.
As you created the first index key for your table in the previous example, Visual FoxPro automatically created a new file, Customer.cdx, to store the new index key. The .cdx index file, called a structural compound index, is the most common and important type of index file you’ll create in Visual FoxPro. The structural .cdx file:
If a Visual FoxPro table has any index file associated with it at all, it is typically a structural .cdx file. The term “structural” refers to the fact that Visual FoxPro treats the file as an intrinsic part of the table and opens it automatically when you use a table. Whether you use the Table Designer or the simplest form of the INDEX command, as shown in the previous example, Visual FoxPro creates the .cdx file with the same base name as the current table, and stores the index information for the new key, or tag, inside. You use the structural .cdx file for frequently used index keys, such as those used for ordering records for daily viewing, data entry, SET RELATION linkages, Rushmore™ optimization on viewing records, or frequently printed reports.
Visual FoxPro offers you two additional types of index files: the non-structural .cdx file and the single-key .idx file. Because the .cdx (or structural compound compact index) is the most important index type, most of the examples in this section will discuss using index keys in the .cdx file to order table records. The other two index file types are less frequently used and are discussed at the end of this section.
You can see how many records are indexed during the indexing process by setting TALK to ON. The record interval displayed during indexing can be specified with SET ODOMETER. For more information about open index files, use the DISPLAY STATUS command. This command lists the names of all open index files, their types (structural, .cdx, .idx), their index expressions, and the name of the master index file or master tag.
The number of index files (.idx or .cdx) you can open is limited only by memory and system resources.
Visual FoxPro supports four types of indexes: primary, candidate, unique, and regular. These index types control whether duplicate values are permitted or prohibited in table fields and records.
A primary index is an index that never permits duplicate values on the specified fields or expression. Primary indexes are principally used within the primary or “referenced” table for establishing referential integrity in a persistent relationship. You can create only one primary index for a table. Visual FoxPro returns an error if you specify a primary index on any field that already contains duplicate data.
A candidate index is an index that never permits duplicate values on the specified fields or expression. The name “Candidate” refers to the status of the index; since candidate indexes prohibit duplicate values, they qualify as “candidates” to be selected as the primary index on a table.
You can create multiple candidate indexes for a table. You use candidate indexes as either the referenced or referencing index in a persistent relationship for establishing referential integrity.
Visual FoxPro returns an error if you specify a candidate index on any field that already contains duplicate data.
You create primary and candidate indexes with the CREATE TABLE or ALTER TABLE commands. You can use both candidate and primary indexes in defining the “one” side of a one-to-many or a one-to-one persistent relationship.
To create a primary or candidate index
-or-
For example, either of the following commands make cust_id the primary key of the customer table:
ALTER TABLE customer ADD PRIMARY KEY cust_id TAG cust_id
ALTER TABLE customer ALTER COLUMN cust_id c(5) PRIMARY KEY
Primary and candidate indexes are stored in the structural .cdx file for a given table. The database stores the names of the indexes in the .cdx file and whether or not the indexes are Primary or Candidate. It is not possible to store these types of indexes in other .cdx files, nor is it possible to use .idx files for these types of indexes. The principal reason is that the index file that contains these indexes should always be open whenever their associated table is opened.
Primary keys are part of a table within a database. If you free a table from a database, the primary key is removed.
If you use a user-defined function in an index expression associated with a database, Visual FoxPro handles the expression in the same way it handles rule and trigger expressions that contain UDFs.
In Visual FoxPro, a unique index does not prevent duplicate values from being created; rather, a unique index stores only the first occurrence of the value in the index file. In this sense, the word “unique” refers to the entries in the index file, which contains only unique values, because it doesn't store a particular key more than once, and ignores the second or later occurrence of a non-unique value. The table indexed by a unique index could contain duplicate values. Unique index types are provided primarily for backward compatibility.
A regular index is simply an index that is not unique, primary, or candidate. You use a regular index for ordering and seeking records, but not to enforce the uniqueness of the data in those records. You also use a regular index as the many side of a one-to-many persistent relationship.
To create a regular index
-or-
For example, the following commands make city a regular key for the customer table:
USE customer
INDEX ON city TAG city
As you work with the records in your table, you’ll discover the need for accessing table records in several different sequences. For example, you might want to order the customer table by contact to quickly find a name you’re looking for, or by postal code to generate mailing labels that are presorted for more efficient mailing.
You can create and store many different ordering scenarios for your table by creating multiple index keys for the same table. This enables you to order table records at different times according to different purposes.
To create additional index keys for a table
-or-
For example, the following code creates two new index keys on the employee table: one on the last_name field and another on the country field:
USE employee
INDEX ON last_name TAG last_name
INDEX ON country TAG country
When you create an index tag without specifying the name of an index file, the tag is added automatically to the table’s structural .cdx index file. The following diagram shows a .cdx index file with three index tags.
.cdx index contains multiple tags representing multiple record-ordering scenarios.
Two of the tags in the diagram, emp_id and last_name, represent indexes based on single fields. The cntry_last index orders records using a simple two-field index expression. For more information on building an index based on multiple fields, see “Indexing on Expressions” later in this chapter.
After you create index keys for the customer table on the company, city, and country fields, you can access and display the table in different orders, simply by choosing the index key you prefer. You use the SET ORDER command to choose a specific index key as the ordering key for the table.
For example, the following code opens a Browse window displaying the records in the customer table in order by country:
SET ORDER TO country
BROWSE
Using the SET ORDER command, you can designate the controlling index file or tag. A table can have many index files open simultaneously. However, you determine the order in which the records in a table are displayed or accessed by setting one single-index (.idx) file (the controlling index file) or tag from a compound index (.cdx) file (the controlling tag) as the controlling index. Certain commands, such as SEEK, use the controlling index tag to search for records. You do not need to SET ORDER for running queries.
You can use SET ORDER at run time to change the order of records in a form. For example, you might want to enable your application’s users to reorder the records in a grid by clicking on the header of the column they want to order by.
To sort the records in a grid by columns
For example, if you created a form based on the Customer table in the Testdata database with a grid containing four columns — company, contact, postal code, and phone — the grid would first appear sorted alphabetically because the records in that table were entered alphabetically.
Customer table in grid, ordered alphabetically by company name
You could then enable the user to view the grid in contact or postal_code order by inserting the following code in the Click event of each column header:
Sample event code to order records in a Grid by clicking on the column header
| Code | Comment |
|
In the Company header Click event code, reorder the grid by the company index key and refresh the form to display records in order by company. |
|
In the Contact header Click event code, reorder the grid by the contact index key and refresh the form to display records in order by contact name. |
|
In the Postal_Code header Click event code, reorder the grid by the postalcode index key and refresh the form to display records in order by postal code. |
Because sorting by phone number is not relevant to this application, leave the Phone header Click event code blank. |
In this example, when the form is first displayed, the grid appears in alphabetical order by company. When the user clicks the header of the Contact column, Visual FoxPro displays the records in the grid in alphabetical order by contact name.
Customer table in grid, reordered alphabetically by contact name
If the user clicks on the Postal_code column header, the grid is resorted and displayed in order by postal code.
Customer table in grid, reordered by postal code
Since there isn't a pressing need in our example application to sort contacts by phone numbers, no SET ORDER code is inserted into the Click event for the phone column header. When the user clicks the Phone column header, the grid display does not change.
In addition to the most common index — the compact compound structural .cdx index — Visual FoxPro supports two other types of index files: the non-structural .cdx, and the stand-alone .idx index. Non-structural .cdx indexes are used for less frequently used multiple-key tags. Stand-alone, or .idx indexes are used for temporary or infrequently used single-key indexes, and are available primarily for backward compatibility.
The following table is a summary of the three index types, how they are named, the number of keys they can contain, and the character limitations for each.
Visual FoxPro Index Types
| Index type | Description | Number of keys | Limits |
| Structural .cdx | Uses same base name as the table file name; opens with table automatically | Multiple-key expressions, called tags |
240-character limit on evaluated expression |
| Non-structural .cdx | Must be opened explicitly; uses a different name from the base table name | Multiple-key expressions, called tags |
240-character limit on evaluated expression |
| Stand-alone .idx | Must be opened explicitly; base name of .idx file is user-defined | Single key expression | 100-character limit on evaluated expression |
A non-structural .cdx index is useful when you want to create multiple index tags for a special purpose, but don’t want to burden your application with maintaining these indexes on an ongoing basis. For example, your application may have a special set of reports that analyzes data based on fields not normally indexed. Your application program can create a non-structural .cdx index with the necessary index tags, run the special reports, then delete the non-structural .cdx file.
To create a non-structural .cdx index tag
You use the OF clause with the INDEX command to direct Visual FoxPro to store the tag in a file other than the structural .cdx index file for the table. For example, the following command creates tags called title and hire_date on the employee table and stores them in a non-structural .cdx file named QRTLYRPT.CDX:
USE employee
INDEX ON title TO TAG title OF QRTLYRPT
INDEX ON hire_date TO TAG hiredate OF QRTLYRPT
The stand-alone index file, based on a single key expression, is stored as an .idx file. In contrast to .cdx indexes, which can store multiple key expressions, the .idx index stores only a single key expression.
You typically use stand-alone indexes as temporary indexes, creating or re-indexing them right before you need them. For example, you may have an index that you use only for a quarterly or annual summary report. Rather than include this infrequently used index in the structural .cdx, where it would be maintained every time you use the table, you can create a stand-alone .idx index. You can create as many .idx files as you want for a particular table.
To create a stand-alone .idx index
Using the INDEX command with the COMPACT clause creates a new stand-alone index in a small, quickly accessed index file. You can omit the COMPACT clause if you want to create a non-compact stand-alone .idx file for compatibility with the older FoxBASE+® and FoxPro® version 1.0 index formats.
The following code creates a stand-alone .idx file on order_date in the orders table, sets the order to the new index, then opens a Browse window showing the orders in order_date sequence:
USE ORDERS
INDEX ON order_date TO orddate COMPACT
SET ORDER TO orddate
BROWSE
You can use the COPY TAG command to generate a stand-alone index file from an index tag in an existing .cdx file. For example, you may find that one of the indexes you currently maintain in the structural .cdx is used only for quarterly or annual reports. The following code creates a stand-alone index from a tag birth_date in the employee table:
COPY TAG birth_date to birthdt COMPACT
After you’ve created a stand-alone index from a tag in a .cdx file, you’ll typically delete this now unneeded tag from the .cdx file. The next section describes deleting an index.
You can delete indexes you’re no longer using by deleting the tag within the .cdx file, or by deleting the .idx file itself for stand-alone indexes. Deleting unused index tags improves performance by removing the need for Visual FoxPro to update unused tags to reflect changes in a table’s data.
You can remove a tag from the structural .cdx file using the Table Designer or the language.
To delete an index tag in the structural .cdx
-or-
-or-
For example, if your employee table contained a tag called title, you could delete it using the following code:
USE employee
DELETE TAG title
If the tag you wanted to delete was the primary key for the employee table, you could use the ALTER TABLE command:
USE employee
ALTER TABLE DROP PRIMARY KEY
A non-structural .cdx index and its tags are not visible in the Table Designer. You use the language to delete a tag from a non-structural .cdx file.
To delete an index in a non-structural .cdx file
You use the OF clause with the DELETE TAG command to direct Visual FoxPro to delete a tag from a .cdx other than the structural .cdx file. For example, if you have a non-structural .cdx file named QRTLYRPT.CDX with a tag called title, you could delete the title tag with the following command:
DELETE TAG title OF qtrlyrpt
You delete all the tags in a structural or non-structural .cdx file with the ALL clause of the DELETE TAG command.
Because a stand-alone index file contains only a single index key expression, you delete the expression by deleting the .idx file from disk.
To delete a stand-alone .idx file
For example, the following code deletes the stand-alone .idx index file Orddate.idx:
DELETE FILE orddate.idx
You could also use a utility, such as Windows Explorer, to delete an unneeded stand-alone .idx file.
You can increase the power of your applications by creating indexes based on expressions. These expressions can be simple or complex, depending upon what you want to accomplish.
Simple index expressions are indexes based on single fields, or on the concatenation of two or more character fields to form a multifield key. For example, you might want to create an index for the Customer table in the TasTrade database based on the expression:
country + region + cust_id
When you browse the Customer table sorted by this index tag, you see the customers ordered by country, then region, then customer ID.
If you want to prevent duplicating values across multiple fields, you can create a primary or candidate index based on an expression combining multiple fields.
For example, you may have a table that stores the area code and phone number in two columns:
| Area Code | Phone Number |
| 206 | 444-nnnn |
| 206 | 555-nnnn |
| 313 | 444-nnnn |
Both the area code field and the phone number field contain values that duplicate the values in other rows. However, no phone numbers are duplicated, because it is the combination of the two fields that makes up the value. Yet, if the primary or candidate index specified both columns in the index expression, the rows in the example would not be considered duplicates. If you attempted to enter a value that was exactly the same area code and phone number as one of the existing rows, Visual FoxPro would reject the entry as a duplicate.
You can create indexes on fields that contain null values. Index expressions that evaluate to .NULL. are inserted into the .cdx or .idx file before non-null entries. All null values are placed at the beginning of the index.
The following example demonstrates one effect of indexing null values. This is the state of the table before the index is applied:
Null values appear in the SocSec field for two records.
The value .NULL. in two records represents the fact that Social Security numbers for Anne Dunn and Alan Carter are either unknown or unavailable. You then create an index with the Social Security number using the following example:
INDEX ON SocSec + LastName + FirstName TAG MyIndex
When you view the table sorted by this index, you see the sort order as shown in the following figure.
After indexing on SocSec, records containing null SocSec values appear first.
When the index expression contains null values, the records whose SocSec values are .NULL. are sorted first (by LastName), followed by the records whose SocSec values are non-null. Notice that there are two entries for Alan Carter. Because record 5 contains a null value, record 5 is indexed before record 2.
You can also create indexes based on more complex expressions. Visual FoxPro index key expressions can include Visual FoxPro functions, constants, or user-defined functions.
The expression you create must evaluate to no more than 100 characters for a stand-alone (.idx) index or 240 characters for a .cdx index tag. You can use fields of different data types together in a single tag by converting the individual components of the expression to character data.
To take advantage of Rushmore™ optimization, the index expression must exactly match the criteria.
You can use Visual FoxPro functions in an index tag. For example, you can use the STR( ) function to convert a numeric value into a character string. If you wanted to create an index tag for the customer table that combined the cust_id field with the maxordamt field, you could convert the maxordamt field from a Currency field with a width of 8 to an 8-character field with 2 decimal places using the following code:
INDEX ON cust_id + STR(maxordamt, 8, 2) TAG custmaxord
If you want to reduce the size of indexes for fields with integer values, you can convert the integer values to a binary character representation using the BINTOC( ) function. You can also convert the binary values to integer values using the CTOBIN( ) function.
If you want to create an index to sort a table in chronological order, you can use the DTOS( ) function to convert a date field to a character string. To access the employee table by hire_date and emp_id, you can create this index key expression:
INDEX ON DTOS(hire_date) + emp_id TAG id_hired
You can increase the power of your index by referencing a stored procedure or a user-defined function in your index expression. For example, you can use a stored procedure or a UDF to extract the street name from a single field that includes both the street number and street name. If the street number is always numeric, the stored procedure or UDF can return the character portion of the field and pad the field with spaces as needed to create a constant-length index key. You can then use this index key to access records in the table in street-name order.
You may prefer to use a stored procedure rather than a UDF in your index tag, if your table is associated with a database. Because a UDF is stored in a file that is separate from the database, it is possible to move or delete the UDF file, which then causes the index tag referencing the UDF to become invalid. In contrast, stored procedure code is stored in the .dbc file and can always be located by Visual FoxPro.
Another benefit to using a stored procedure in an index tag is that referencing a stored procedure guarantees that the index is based on the exact code you specify. If you use a UDF in your index expression, any UDF that is in scope at the time of indexing and has the same name as the UDF referenced in your index will be used.
Note Exercise care when referencing a stored procedure or UDF in an index expression, as it increases the time required to create or update the index.
You can create an index tag that refers to a table open in another work area. It’s wise to use a stand-alone index (.idx) for any tag that refers to more than one table. This is because if you were to include a tag referring to another table in a structural .cdx file, Visual FoxPro wouldn’t allow you to open the table until you opened the table referenced in the index tag.
You can view records in descending order by creating a descending index, or by reading an existing index in descending order.
To create a descending index
-or-
To create a compound structural index file, you can use either method. To create other types of index files, you can use the second method. For example, you could create a new descending index ordering your product table from highest to lowest unit_price and browse the table in the new order with the following code:
USE products
INDEX ON unit_price TAG unit_price DESCENDING
BROWSE
To read an existing index in descending order
Reading an existing index in descending order enables you to leverage an existing index rather than create a new one. For example, you may have already created an index ordering your product table by unit_price with the following code:
USE products
INDEX ON unit_price TAG unit_price
By default, the order is ascending. You could browse the table in descending order with the following code:
USE products
SET ORDER TO unit_price DESCENDING
BROWSE
The previous examples focus on accessing information in descending order. Both the SET ORDER and INDEX commands also offer an ASCENDING clause. You can combine these two commands to gain tremendous flexibility in your application. For example, if you use the ASCENDING or DESCENDING clause to create an index in the most frequently used order, you can then use the opposite clause with the SET ORDER command to view or access the information in the opposite order, when that order is more convenient.
You can limit the records you access to only the data you want by using a filtered index. When you create a filtered index, only records that match the filter expression are available for display and access.
To filter data using a filtered index
-or-
If you include the optional FOR clause with the INDEX command, the index file acts as a filter on the table. Index keys are created in the index file for just those records that match the filter expression. For example, if you were preparing a mailing to go to the sales representatives in your company, and you wanted to sort the mailing by country, you could create an index that filtered the employee table so that only the records for sales representatives appeared, ordered by their country and their last name. The following code creates a filtered index and displays the filtered data in a Browse window:
USE employee
INDEX ON country+last_name FOR title = "Sales Representative" ;
TAG reps_cntry
BROWSE
When you view the Browse window, only the sales representatives are shown; the records for other employees do not appear at all in the Browse window.
A filtered index builds an index only for records that match the filter expression.
You can use the SET FILTER command to filter data temporarily, without building a special filtered index. This command is particularly useful when you want to specify a temporary condition that records in a table must meet in order to be accessible. To turn off the filter for the current table, you can issue SET FILTER TO without an expression. For example, you could issue the following command to filter the customer table to show only the customers in Germany:
USE customer
SET FILTER TO country = "Germany"
BROWSE
The SET FILTER command accepts any valid Visual FoxPro logical expression as the filter condition. Once you issue the SET FILTER command, only the records that satisfy the filter condition are available in the table. All commands that access the table respect the SET FILTER condition. You can set a separate filter for every open table.
You can improve the performance of indexed tables by keeping indexes current and using optimizable expressions in your indexes.
Index files become outdated when you open a table without opening its corresponding index files and make changes to the key fields in the table. Index files can also become invalid as a result of a system crash, or potentially by accessing and updating a table from a program other than Visual FoxPro. When index files become outdated, you can update them by re-indexing with the REINDEX command.
To rebuild an active index file
-or-
For example, the following code updates the index file for the Customer table:
USE customer
REINDEX
REINDEX updates all index files open in the selected work area. Visual FoxPro recognizes each index file type (compound index .cdx files, structural .cdx files, and single index .idx files) and re-indexes accordingly. It updates all tags in .cdx files, and updates structural .cdx files, which open automatically with the table.
You can also update outdated index files with the REINDEX command.
Re-indexing takes time, particularly when you’re re-indexing large tables. You should re-index only when necessary. You can enhance performance by re-indexing during the initialization or termination portion of your program, rather than performing indexing maintenance during the main portion of an application.
You can use indexes to speed queries and other operations. For information on creating Rushmore-optimizable index expressions, see Chapter 15, Optimizing Applications.
To use multiple tables, you use data sessions to control the tables that are available to forms, and work areas to set which tables are open. A work area is a numbered region that identifies an open table. You can open and manipulate Visual FoxPro tables in 32,767 work areas. Work areas are normally identified in your application by using the table alias of the table open in the work area. A table alias is a name that refers to a table open in a work area.
In addition to the work areas visible in the Data Session window, Visual FoxPro automatically provides a separate environment for each instance of a form or form set through data sessions. A data session is a representation of the current dynamic work environment used by a form, form set, or report. Each data session contains its own set of work areas. These work areas contain the tables open in the work areas, their indexes, and relationships. For information on using data sessions, see Chapter 17, Programming for Shared Access.
You can see the list of tables open in a Visual FoxPro session by opening the Data Session window.
To open the Data Session window
-or-
When you enter SET in the Command window, Visual FoxPro opens the Data Session window and displays the work area aliases for the tables open in the current data session.
Data Session window with the Employees table open
You can open a table in a work area with the Data Session window or with the USE command.
To open a table in a work area
-or-
To open a table in the lowest available work area, use the IN clause of the USE command with work area 0. For example, if tables are open in work areas 1 through 10, the following command opens the customer table in work area 11.
USE customer IN 0
You can also choose Open from the File menu to open a table in a work area.
You can close a table in a work area through the Data Session window or by using the language.
To close a table in a work area
-or-
-or-
When you issue the USE command without a table name and a table file is open in the currently selected work area, the table is closed. For example, the following code opens the customer table, displays a Browse window, and then closes the table:
USE customer
BROWSE
USE
You also close a table automatically when you open another table in the same work area, or issue the USE command with the IN clause and reference the current work area. The following code opens, displays, and then closes the customer table by issuing USE IN and the table alias customer:
USE customer
BROWSE
USE IN customer
You can’t have more than one table open in a work area at one time.
You can reference the next available work area before you open a table by using the work area number as shown below:
SELECT 0
A table alias is the name that Visual FoxPro uses to refer to a table open in a work area. Visual FoxPro automatically uses the file name for the default alias when you open a table. For example, if you open the file Customer.dbf in work area 0 with the following commands, the default alias customer is automatically assigned to the table:
SELECT 0
USE customer
You can then use the alias customer to identify the table in a command or function. You can also create your own alias.
You can assign your own user-defined alias to a table when you open it.
To open a table with a user-defined alias
For example, to open the file Customer.dbf in work area 0 and assign it an alias of people, use the following command:
SELECT 0
USE customer ALIAS people
You must then use the alias people to refer to the open table. An alias can consist of up to 254 letters, digits, or underscores and it must begin with a letter or an underscore. Visual FoxPro automatically creates an alias if the alias you provide contains an unsupported alias character.
Visual FoxPro automatically assigns an alias to a table in certain instances:
The default aliases assigned in the first 10 work areas are the work area letters “A” through “J”; the aliases assigned in work areas 11 through 32767 are W11 through W32767. You can use these Visual FoxPro-assigned aliases just as you would any default or user-defined alias to refer to a table open in a work area.
You can move to a work area from another work area with the SELECT command. For example, if Customer.dbf is open in a work area and the default alias of CUSTOMER is assigned, you can move to this work area with the following SELECT command:
SELECT customer
You can also refer to fields in other work areas by prefacing the field name with the alias name and a period, or the –> operator. For example, if you’re in a work area and you want to access the field contact from the Customer table open in a different work area, you could use the following to reference the field:
customer.contact
If the table you want to reference is opened with an alias, you can use the alias name. For example, if the Customer table is opened with the alias people, you can reference the lastname field with the following to refer to the field:
people.lastname
Using the table name or table alias specifically identifies the desired table independently from the work area in which the table is open.
When you establish a temporary relationship between tables, you cause the record pointer of one table (the child table) to automatically follow the movements of the record pointer in the other, or parent, table. This allows you to select a record on the “one” or parent side of a relationship and automatically access the related records on the “many” or child side of the table relationship.
For example, you may want to relate the customer and orders tables so that when you move the record pointer in the customer table to a particular customer, the record pointer in the orders table moves to the record with the same customer number.
You can use table work areas and table aliases to establish relationships between two open tables with the SET RELATION command. If you’re using a form to work with tables, you can store these relationships as part of the data environment for the form.
You can use the Data Session window or the language to create temporary relationships between tables.
To temporarily relate tables
-or-
You use the SET RELATION command to establish a relationship between a table open in the currently selected work area, and another table open in another work area. You typically relate tables that have a common field, and the expression you use to establish the relationship is usually the index expression of the controlling index of the child table.
For example, a customer may have many orders. If you create a relationship between the field that is common to both customer and order tables, you can easily see all the orders for any customer. The following program uses a field, cust_id, that is common to both tables and creates a relationship between the two tables based on the field cust_id in the customer table and the cust_id index tag in the orders table.
Using SET RELATION to Establish Relationship Between Two Tables
| Code | Comments |
|
Open the customer table (parent table) in work area 1. |
|
Open the orders table (child table) in work area 2. |
|
Select the child work area. |
|
Specify the table order for the child table using the index tag cust_id. |
|
Select the parent work area. |
|
Create the relationship between the parent table and the controlling index in the child table. |
|
Open two Browse windows; notice that moving the record pointer in the parent table changes the set of data viewed in the child table. |
The Data Session window displays the two open tables, Orders and Customer, and the relationship established by the SET RELATION command.
The Data Session window displays open table aliases and temporary relationships.
You created an index on the child table, orders, to organize records in the orders table into groups, according to the customer who placed the order. When you create a relationship between the parent table and the index of the child table, Visual FoxPro selects only those child table records whose index key matches the index key of the parent record you’ve selected.
The previous example established a single relationship between two tables. You can also use the SET RELATION command to establish multiple relationships between a single parent table and various child tables.
If you are creating a form that uses more than one table, you can use the data environment to create table relationships and store them with the form. Relationships you establish in the data environment are opened automatically when you run the form. For information on creating a data environment, see Chapter 9, Creating Forms.
You can also create a relationship between records in a single table. This relationship, known as a self-referential relation, can be useful in situations where you have all the information you need stored in a single table. For example, you may want to move through the managers in the Employees table and have the employees who report to each manager automatically change as you move the record pointer from manager to manager.
To temporarily relate records in a single table
-or-
To create a self-referential relation, open the same table twice: once in one work area and then a second time, with the USE AGAIN command, in another work area. Then use an index to relate the records. For example, the following code establishes and browses a self-referential relationship by creating an index tag named mgr_id that orders the Employee table by the reports_to field:
SELECT 0
USE employee ALIAS managers
SELECT 0
USE employee AGAIN ALIAS employees
INDEX ON reports_to TAG mgr_id
SET ORDER TO mgr_id
SELECT managers
SET RELATION TO emp_id INTO employees
BROWSE
SELECT employees
BROWSE
When you move the record pointer in the managers Browse window, the employees Browse window is refreshed to show only those employees who report to the selected manager.
Indexes are used to establish persistent relationships between tables in a database. Persistent relationships are relationships between database tables that are stored in the database file and are automatically used as default join conditions in the Query and View Designers. Persistent relationships are also displayed in the Database Designer as lines joining table indexes, and as default relationships when you use the tables in the data environment.
Unlike temporary relationships set with the SET RELATION command, persistent relationships do not need to be re-created each time you use tables. However, because persistent relationships do not control the relationship between record pointers in tables, you will use both temporary SET RELATION relationships as well as persistent relationships in developing Visual FoxPro applications. For more information on setting persistent relationships, see Chapter 6, Creating Databases.