Choosing Data Types

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:

• What kind of values to allow in the field. For example, you can’t store text in a Numeric field. How much storage space Visual FoxPro is to set aside for the values stored in that field. For example, any value with the Currency data type uses 8 bytes of storage.
• What types of operations can be performed on the values in that field. For example, Visual FoxPro can find the sum of Numeric or Currency values but not of Character or General values.
• Whether Visual FoxPro can index or sort values in the field . You can’t sort or create an index for Memo or General fields.

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

• In the Table Designer, choose a data type from the Type list.

-or-

• Use the CREATE TABLE command.

For example, to create and open the table products with three fields, prod_id, prod_name, and Values 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

• In the Table Designer, select or clear the Null column for the field. When the Null column is selected, you can enter null values in the field.

-or-

• Use the NULL and NOT NULL clauses of the CREATE TABLE command.

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

• In the Table Designer, select the Null column for each table field.

-or-

• Use the SET NULL ON command before using the CREATE TABLE command.

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.

Visual FoxPro Fields

Creating Fields
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.


Naming Fields
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

• In the Table Designer, enter a field name in the Name box.

-or-

• Use the CREATE TABLE command or ALTER TABLE command.

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))

Using Short Field Names
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”.

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.

Saving a Table as HTML

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
1. Open the table.
2. Browse the table by issuing the BROWSE command in the Command window or by choosing Browse from the View menu.
3. Choose Save As HTML on the File menu.
4. Enter the name of the HTML file to create and choose Save.

Copying and Editing Table Structure

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
1. Open the original table.
2. Use the COPY STRUCTURE EXTENDED command to produce a new table containing the structural information of the old table.
3. Edit the new table containing the structural information to alter the structure of any new table created from that information.
4. Create a new table using the CREATE FROM command.
    The new table is empty.
5. Use APPEND FROM or one of the data copying commands to fill the table if necessary.

Duplicating a Table

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
1. Open the original table.
2. Use the COPY STRUCTURE command to make a copy of the original table.

3. Open the empty table created with the COPY STRUCTURE command.
4. Use the APPEND FROM command to copy the data from the original table.

Deleting a Free Table

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

• In the Project Manager, select the free table, choose Remove, and then choose Delete.

-or-

• Use the DELETE FILE command.

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

Deleting a Database Table

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 Creating Databases.

To delete a database table from disk

• In the Project Manager, select the table name, choose Remove, and then choose Delete.

-or-

• From the Database Designer, select the table, choose Remove from the Database menu, and then choose Delete.

-or-

• To delete the table plus all primary indexes, default values, and validation rules associated with the table, use the DROP TABLE command.

-or-

• To delete just the table file (.dbf), use the ERASE command.

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.

To rename a free table

Use the RENAME command.

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.

Renaming a Table

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

1. In the Database Designer, select the table to rename.
2. From the Database menu, choose Modify.
3. In the Table Designer, type a new name for the table in the Table Name box on the Table tab.

Naming a Table

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

• In the Table Designer, enter a long name in the Table Name box.

-or-

• Use the NAME clause of the CREATE TABLE command.

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.

Creating a Free Table

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

• In the Project Manager, select Free Tables, and then New to open the Table Designer.

-or-

• Use the FREE keyword with the CREATE TABLE command.

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.

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

• In the Project Manager, select a database, then Tables, and then New to open the Table Designer.

-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))

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.

Creating 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, fieldand
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.

Some features apply only to database tables. For information about associating tables with a database,
see 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 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