Choosing Data Types

When you create a field in a table, you can choose the type of data that the field stores. When you choose the data type for a field, you need to consider the following:

• What kind of values the field allows. For example, you cannot store text in a Numeric field.

• How much storage space Visual FoxPro needs 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 calculate the sum of Numeric or Currency values but not the sum of Character or General values.

• Whether Visual FoxPro can index or sort values in the field. You cannot sort or create an index for Memo or General fields.

  Tip   For phone numbers, part numbers, and other numbers you do not intend to use for mathematical calculations, you should select the Character data type, not the Numeric data type.

Choosing a Data Type

Each field in your table holds a particular type of data. You can set the following data types for a field in a table.

Visual FoxPro Field Data Types

For more information about these data types, see Data and Field Types Overview.

To specify a data type for a field

1. In the Table Designer, create or choose a field in the Fields tab.
2. From the Type list, select a data type for the field.

For more information about the Table Designer, see Fields Tab, Table Designer.

You can also programmatically specify a data type for a field by using the CREATE TABLE command. 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.

Adding a Regular Index Quickly

When 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.

Specifying Null Values

When you build a new table, you can specify whether one or more table fields accept null (.NULL.) values. When you use a null value, you are indicating the fact that information usually stored in the field or record is not currently available.

For example, an employee's health benefits or tax status might be unknown at the time a record is populated. Instead of storing a zero or a blank, which could be interpreted to have meaning, you could store a null value in the field until more information becomes available.

To specify whether null values can be entered in a field

• In the Fields tab of the Table Designer, select or clear the check box in the NULL column for the field.

When the check box in the NULL column is selected, you can enter null values in the field.

You can also programmatically indicate that the field accepts null values by specifying the NULL and NOT NULL clauses when using 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 programmatically control whether null values are permitted in table fields by using the SET NULL command.

To permit null values in all fields of the table

• In the Fields tab of the Table Designer, select the check box in the NULL column for every field.

You can also programmatically allow null values in all fields by using the SET NULL command before using the CREATE TABLE command.

When you issue SET NULL ON, Visual FoxPro automatically selects the check box in the NULL column for each table field when you add fields in the Table Designer. If you call SET NULL before CREATE TABLE, you do not have to specify the NULL or NOT NULL clauses. For example, the following code creates a table that allows null values in every table field:

SET NULL ON
CREATE TABLE test (field1 C(6), field2 C(40), field3 Y)

Note   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, appended fields containing null values are treated as blank, empty, or zero in the current table.

Adding Comments to Fields

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

1. In the Table Designer, select the field.

2. In the Field Comment box, enter the text for your comment.

3. Choose OK.

   -or-

• Use the DBSETPROP( ) function.

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

See Also

Naming Fields | Creating Default Field Values | Working with Tables | Table Designer | CREATE TABLE | Data and Field Types | Handling Null Values | Developing Databases | Restricting Access to Fields