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:
•
Creating Tables•
Working with Records•
Indexing Tables•
Using Multiple TablesCreating Tables
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.
Designing Database vs. Free Tables
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:
•
Long names for the table and for each field in the table.•
Captions and comments for each table field.•
Default values, input masks, and format for table fields.•
Default control class for table fields.•
Field-level and record-level rules.•
Primary key indexes and table relationships to support referential integrity rules.•
One trigger for each INSERT, UPDATE, or DELETE event.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. 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
Creating a Database 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
–
•
Use the CREATE TABLE command with a database open.For example, the following code creates the table
smalltbl with one column, called name:
OPEN DATABASE Sales
CREATE TABLE smalltbl (name c(50))
by a backlink stored in the table’s header record.
Modifying the Table Structure
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
•
In the Project Manager, select the table name and then choose Modify.–
Or
–
•
In the Database Designer, select the table in the schema and choose Modify from the Database menu.–
Or
–
•
Use the MODIFY STRUCTURE command.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
•
Use the ALTER TABLE command.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.
Adding Fields
You can add a new field to a table with the Table Designer or with the language.
To add a field to a table
•
In the Table Designer, choose Insert.–
Or
–
•
Use the ADD COLUMN clause of the ALTER TABLE command.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
Deleting Fields
You can delete an existing field from a table with the Table Designer or with the language.
To delete a field from a table
•
In the Table Designer, select the field and choose Delete.•
Use the DROP COLUMN clause of the ALTER TABLE command.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.
Working with Records
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.
Adding Records
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
•
Use the INSERT - SQL command.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:
•
An open table.•
An existing record.•
The name of the field in which to store the value.•
A value for each field that is valid for the field’s data type.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.
Adding Records in Browse Mode
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.
Entering Data in a Table
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 multiuser
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..
Editing Records in a Table
You can display and edit existing records in a table through the interface or programmatically.
To display records for editing
•
Use the EDIT command.–
Or
–
•
Use the CHANGE command.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.
Deleting Records
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.
Marking Records for Deletion
You can mark records for deletion through the interface or with the DELETE - SQL command.
To mark a record for deletion
•
In a Browse window, click the deletion marker to flag the record.–
Or
–
•
From the Table menu, choose Delete Records.–
Or
–
•
Use the DELETE - SQL command.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.
Retrieving Records Marked for Deletion
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
•
In a Browse window, click the deletion marker to unmark the record.–
Or
–
•
From the Table menu, choose Recall Records.•
Use the RECALL command.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.
Removing Records Marked for Deletion
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
•
In a Browse window, choose Remove Deleted Records from the Table menu.–
Or
–
•
Use the PACK command.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.
Conserving Space
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.
Removing All Records from a 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.
Indexing Tables
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.
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
•
In the Table Designer, choose the Index tab and enter the information for one index key. ChooseRegular as the index type.
–
Or
–
•
Use the INDEX command.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.
Creating an Index File
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:
•
Opens automatically when you open a table.•
Can contain multiple ordering scenarios, or index keys, in the same index file.•
Is automatically maintained as you add, change, or delete table records.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.
The number of index files (.IDX or .CDX) you can open is limited only by memory and system resources.
Controlling the Order in which Records Are Accessed
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
Setting Record Order at Run-Time
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.
Setting Record Order Interactively in a Form
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
1
Create a form with a Grid control.
2